Flashback - 10g or Later

Today i need to post on flashback feature on oracle database (10g or later)

Version: 10.2.0.1

One of my friend needed step by step configuration of Flashback. Here we go..


Pre-Requisites:
1. Database should be on archivelog mode

Startup the database with mount option:
(Simple Example of the procedure)

connect as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 19 14:40:28 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5


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

SQL> startup mount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 184553284 bytes
Database Buffers 411041792 bytes
Redo Buffers 7163904 bytes
Database mounted.

Enable the Flashback:

SQL> alter database flashback on;

Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1G;

System altered.

SQL> alter database open;


Now connect as a normal user who has default tablespace as someone other than SYSTEM

SQL> conn balaji/balaji
connected

SQL> create table test (id number(2), name varchar(30));

Table created.

SQL> insert into table test values (3, 'INDIA');
1 row inserted

SQL> insert into table test values(4,'SRILANKA');
1 row inserted

SQL> select * from test;

ID NAME
---------- --------------------
3 INDIA
4 SRILANKA

SQL> select tablespace_name from dba_segments where segment_name like 'TEST';

TABLESPACE_NAME
------------------------------
USERS

Note the user balaji has the default tablespace as USERS.

Drop the table test:

SQL> drop table test;

Table dropped.


SQL> select * from test;
ERROR at line 1:
ORA-00942: table or view does not exist


Using Flashback to get the table from recycle bin:

SQL> flashback table "TEST" to before drop;

Flashback complete.

SQL> select * from test;

ID NAME
---------- --------------------
3 INDIA
4 SRILANKA

SQL>

Points to remember:
Flashback option will not work for SYSTEM tablespace objects.

You will receive:
SQL> flashback table "TEST" to before drop;
flashback table "TEST" to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

This is not about SYS objects, this is about SYSTEM tablespace... I can create a sys.table (logged as sys) on USER tablespace and then recover it through flashback.

Recycle functionality is available only for non-system locally-managed tablespaces.

Hope it helps... Cheers!!!

ORA-25153: Temporary Tablespace is Empty has been detected

The Following Error was detected while doing Cloning of DB Tier.

The actual Fact was:
The server was heavily loaded, so the control file creation terminated unsuccessfully. So manually created the control file and opened the database.

After this, I began run adconfig.sh

$cd $ORACLE_HOME/appsutil/bin
$ ./adconfig.sh
[oracle@prod bin]$ ./adconfig.sh
Enter the full path to the Context file: /oracle/PROD/db/tech_st/11.1.0/appsutil/UPGRDE_prod.xml
Enter the APPS user password:
The log file for this session is located at: /oracle/PROD/db/tech_st/11.1.0/appsutil/log/UPGRDE_prod/11081336/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
Using ORACLE_HOME location : /oracle/PROD/db/tech_st/11.1.0
Classpath : :/oracle/PROD/db/tech_st/11.1.0/jdbc/lib/o jdbc5.jar:/oracle/PROD/db/tech_st/11.1.0/appsutil/java/xmlparserv2.jar:/oracle/PROD/db/tech_st/11.1.0/appsutil/java:/oracle/PROD/db/tech_st/11.1.0/jlib/netcfg.jar:/oracle/PROD/db/tech_st/11.1.0/jlib/ldapjclnt11.jar

Using Context file : /oracle/PROD/db/tech_st/11.1.0/appsutil/UPGRDE_prod.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...ERROR: InD bCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.append_ctx_ fragment(:1,:2,:3); END;: 1; Oracle error -25153: ORA-25153: Temporary Tablespac e is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm _util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -25153: ORA-25153: Temporary Tablespace is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
at oracle.apps.ad.autoconfig.oam.InDbCtxFile.uploadCtx(InDbCtxFile.java: 249)
at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.uploadToDb(CtxSynchroni zer.java:328)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBCtx(File SysDBCtxMerge.java:678)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(Fi leSysDBCtxMerge.java:222)
at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:16 88)
at oracle.apps.ad.context.CtxValueMgt.main(CtxValueMgt.java:763)
FAILED


So after creating the controlfile, i did not created a tempfile.

So,
sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 8 13:45:51 2010

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$tempfile;

no rows selected

SQL> alter tablespace TEMP1 add tempfile '/oracle/PROD/db/apps_st/data/TEMP01.dbf' size 500M autoextend on;

Tablespace altered.

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS
---------- ---------------- ------------------ ---------- ---------- -------
ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
1 5.9652E+12 08-NOV-10 284 1 ONLINE
READ WRITE 524288000 64000 524288000 8192
/oracle/PROD/db/apps_st/data/TEMP01.dbf


After this, again run adconfig.sh on DB Tier

[oracle@prod bin]$ ./adconfig.sh
Enter the full path to the Context file: /oracle/PROD/db/tech_st/11.1.0/appsutil/UPGRDE_prod.xml
Enter the APPS user password:
The log file for this session is located at: /oracle/PROD/db/tech_st/11.1.0/appsutil/log/UPGRDE_prod/11081348/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
Using ORACLE_HOME location : /oracle/PROD/db/tech_st/11.1.0
Classpath : :/oracle/PROD/db/tech_st/11.1.0/jdbc/lib/ojdbc5.jar:/oracle/PROD/db/tech_st/11.1.0/appsutil/java/xmlparserv2.jar:/oracle/PROD/db/tech_st/11.1.0/appsutil/java:/oracle/PROD/db/tech_st/11.1.0/jlib/netcfg.jar:/oracle/PROD/db/tech_st/11.1.0/jlib/ldapjclnt11.jar

Using Context file : /oracle/PROD/db/tech_st/11.1.0/appsutil/UPGRDE_prod.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db111
Updating rdbms type in Context file to 32 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.
[oracle@prod bin]$


Cheers... Hope this helps...