FLASHBACK TECHNOLOGY IN ORACLE

Flashback feature  actually  we can go in past to see and recover data .
flashback use undo data


*Flashback Query
*Flashback Version Query
*Flashback Transaction Query
*Flashback Table
*Flashback Drop (Recycle Bin)

Flashback Query:

SQL>CREATE TABLE test (id  NUMBER(10));

SQL>SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
SQL>SELECT  TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
195300    2014-03-12 13:11:56

SQL>insert into test (id number(10));
SQL> commit

SQL>select * from test;

SQL>select * from test as of TIMESTAMP TO_DATE('2014-03-12 13:06:22','YYYY-MM-DD HH24:MI:SS')

SQL>select * from test as of TIMESTAMP TO_DATE('2014-03-12 13:02:00','YYYY-MM-DD HH24:MI:SS')

SQL>select * from test as of scn 195300


Flashback Version Query:
Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.


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

SQL>SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
 195454     2014-03-12 13:26:00

SQL>insert into test values('&id','&name');

1  anurag

SQL>commit;
SQL>update test set name='a' where id=1;
SQL>commit;
SQL>update test set name='b' where id=1;
SQL>commit;

SQL>SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
 195641     2014-03-12 13:30:00


SQL>SELECT versions_startscn,versions_starttime,
       versions_endscn,versions_endtime,
       versions_xid,versions_operation,name
FROM   test
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2014-03-12 13:25:00','YYYY-MM-DD HH24:MI:SS')
       AND TO_TIMESTAMP('2014-03-12 13:30:00','YYYY-MM-DD HH24:MI:SS')
WHERE ID=1;



About pseudocolumn details

VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN ot TIMESTAMP.

VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
VERSIONS_XID - ID of the transaction that created the row in it's current state.
VERSIONS_OPERATION - Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)


Flashback Transaction Query:

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries.
The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view.


SQL>SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
          FROM   flashback_transaction_query
         WHERE  xid = HEXTORAW('05001B0029000000');

Flashback Table:
The FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements.
You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
There must be enough information in the undo tablespace to complete the operation.
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).


SQL>FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-03-12 13:26:00', 'YYYY-MM-DD HH24:MI:SS');

SQL>alter table test enable row movement

SQL>select * from test



Flashback Drop (Recycle Bin):


SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL> drop table test;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$9GaQ+m3ZBzngROQfE7OXKA==$0 TABLE        2014-03-12:15:06:08



SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$9GaQ+m3ZBzngROQfE7OXKA==$0 TABLE


SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

        ID
----------
         1

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE



PURGE TABLE tablename;                   -- Specific table.
PURGE INDEX indexname;                   -- Specific index.
PURGE TABLESPACE ts_name;                -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username;  -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN;                        -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN;                    -- The whole recycle bin.


Several restrictions apply relating to the recycle bin.

Only available for non-system, locally managed tablespaces.
There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
The objects in the recycle bin are restricted to query operations only (no DDL or DML).
Flashback query operations must reference the recycle bin name.
Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
Tables with Fine Grained Access policies aer not protected by the recycle bin.
Partitioned index-organized tables are not protected by the recycle bin.
The recycle bin does not preserve referential integrity.

//if default tablespaece is system
if default tablespace not exist then by default system tablespace is default tablespace for any new user.
if default tabelespace system  then recyclebin not working

//make non system tablespace for recycelbin

This feature can be disabled/enabled at the session or system level.

-- Session level.
ALTER SESSION SET recyclebin = OFF;
ALTER SESSION SET recyclebin = ON;

-- System level.
ALTER SYSTEM SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = ON;

CREATE DATABASE MANUALLY IN ORACLE 10g -STEP BY STEP

Step by step create database manually  in oracle 10g

bash-3.2$ echo $ORACLE_HOME

/opt/oracle/db01/app/oracle/product/10.2.0

bash-3.2$

bash-3.2$

bash-3.2$ echo $ORACLE_BASE

/opt/oracle/db01/app/oracle

 //Create following directory

bash-3.2$cd /opt/oracle/db01/app/oracle/admin

bash-3.2$  mkdir adump 

bash-3.2$  mkdir  bdump 

bash-3.2$  mkdir  cdump 

bash-3.2$  mkdir  dpdump 

bash-3.2$  mkdir  pfile  

bash-3.2$  mkdir udump

//Create following directory

bash-3.2$cd /opt/oracle/db01/app/oracle/oradata/

bash-3.2$ mkdir test

//In Pfile change create init.ora file as per requirmnet

##############################################################################

# Copyright (c) 1991, 2001, 2002 by Oracle Corporation

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192

db_file_multiblock_read_count=16

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Database Identification

###########################################

db_domain=""

db_name=test

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/opt/oracle/db01/app/oracle/admin/test/bdump

core_dump_dest=/opt/oracle/db01/app/oracle/admin/test/cdump

user_dump_dest=/opt/oracle/db01/app/oracle/admin/test/udump

###########################################

# File Configuration

###########################################

control_files=("/opt/oracle/db01/app/oracle/oradata/test/control01.ctl", "/opt/oracle/db01/app/oracle/oradata/test/control02.ctl", "/opt/oracle/db01/app/oracle/oradata/test/control03.ctl")

###########################################

# Job Queues

###########################################

job_queue_processes=10

###########################################

# Miscellaneous

###########################################

compatible=10.2.0.1.0

###########################################

# Processes and Sessions

###########################################

processes=300

sessions=335

###########################################

# SGA Memory

###########################################

sga_target=3812622336

###########################################

# Security and Auditing

###########################################

audit_file_dest=/opt/oracle/db01/app/oracle/admin/test/adump

remote_login_passwordfile=EXCLUSIVE

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=1270874112

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_management=AUTO

undo_tablespace=UNDOTBS1

bash-3.2$ export ORACLE_SID=test

bash-3.2$ sqlplus "/as sysdba"

SQL>startup nomount pfile='/opt/oracle/db01/app/oracle/admin/test/pfile/init.ora';

SQL> create spfile from pfile='/opt/oracle/db01/app/oracle/admin/test/pfile/init.ora';

////////////execute database  script

SQL>create database test

logfile group 1 ('/opt/oracle/db01/app/oracle/oradata/test/redo1.log') size 100M,

            group 2 ('/opt/oracle/db01/app/oracle/oradata/test/redo2.log') size 100M,

            group 3 ('/opt/oracle/db01/app/oracle/oradata/test/redo3.log') size 100M

character set WE8ISO8859P1

national character set utf8

datafile '/opt/oracle/db01/app/oracle/oradata/test/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local

sysaux datafile '/opt/oracle/db01/app/oracle/oradata/test/sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited

undo tablespace undotbs1 datafile '/opt/oracle/db01/app/oracle/oradata/test/undotbs1.dbf' size 100M

default temporary tablespace temp tempfile '/opt/oracle/db01/app/oracle/oradata/test/temp01.dbf' size 100M;

//for creating users
bash-3.2$ sqlplus "/as sysdba"

SQL>alter user sys identified by test;
SQL>alter user system identified by manager;

bash-3.2$ connect /as sysdba

SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

bash-3.2$ connect system/manager

SQL>@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/pupbld.sql

SQL>@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/helpus.sql

CATALOG.SQL: creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.It is located in ORACLE_HOME/rdbms/admin/catalog.sql.

CATPROC.SQL: Runs all scripts required for or used with PL/SQL.It is loacted in ORACLE_HOME/rdbms/admin/catproc.sql.

VI EDITOR COMMANDS

General Startup
 To use vi: vi filename
 To exit vi and save changes: ZZ   or  :wq
 To exit vi without saving changes: :q!
 To enter vi command mode: [esc]


Counts
        A number preceding any vi command tells vi to repeat
 that command that many times.




Cursor Movement

 h       move left (backspace)

 j       move down

 k       move up

 l       move right (spacebar)

 [return]   move to the beginning of the next line

 $       last column on the current line

 0       move cursor to the first column on the 
  current line

 ^       move cursor to first nonblank column on the
  current line

 w       move to the beginning of the next word or 
  punctuation mark

 W       move past the next space

 b       move to the beginning of the previous word 
  or punctuation mark

 B       move to the beginning of the previous word,
  ignores punctuation

        e       end of next word or punctuation mark

        E       end of next word, ignoring punctuation

        H       move cursor to the top of the screen 

        M       move cursor to the middle of the screen

        L       move cursor to the bottom of the screen 




Screen Movement

       G        move to the last line in the file

       xG       move to line x

       z+       move current line to top of screen

       z        move current line to the middle of screen

       z-       move current line to the bottom of screen

       ^F       move forward one screen

       ^B       move backward one line

       ^D       move forward one half screen

       ^U       move backward one half screen

       ^R       redraw screen 
  ( does not work with VT100 type terminals )

       ^L       redraw screen 
  ( does not work with Televideo terminals )




Inserting

       r        replace character under cursor with next 
  character typed

       R        keep replacing character until [esc] is hit

       i        insert before cursor

       a        append after cursor

       A        append at end of line

       O        open line above cursor and enter append mode




Deleting

 x       delete character under cursor

 dd      delete line under cursor

        dw      delete word under cursor

        db      delete word before cursor




Copying Code

        yy      (yank)'copies' line which may then be put by
  the p(put) command. Precede with a count for
  multiple lines.




Put Command
        brings back previous deletion or yank of lines,
 words, or characters

        P       bring back before cursor

        p       bring back after cursor



 Find Commands

 ?       finds a word going backwards

 /       finds a word going forwards

        f       finds a character on the line under the
  cursor going forward

        F       finds a character on the line under the
  cursor going backwards

        t       find a character on the current line going
  forward and stop one character before it

 T       find a character on the current line going
  backward and stop one character before it

 ; repeat last f, F, t, T




Miscellaneous Commands

 . repeat last command

 u undoes last command issued

 U undoes all commands on one line

 xp deletes first character and inserts after
  second (swap)

 J join current line with the next line

 ^G display current line number

 % if at one parenthesis, will jump to its mate

 mx mark current line with character x

 'x find line marked with character x

 NOTE: Marks are internal and not written to the file.




Line Editor Mode
 Any commands form the line editor ex can be issued 
 upon entering line mode.

 To enter: type ':'

 To exit: press[return] or [esc]




ex Commands
 For a complete list consult the 
 UNIX Programmer's Manual




READING FILES
 copies (reads) filename after cursor in file 
 currently editing

 :r filename




WRITE FILE

 :w  saves the current file without quitting




MOVING

 :# move to line #

 :$ move to last line of file




SHELL ESCAPE
 executes 'cmd' as a shell command.

 :!'cmd'

HOW TO CREATE RMAN RECOVERY CATALOG DATABSE

The catalog database is usually a small database it contains and maintains the metadata of all rman backups performed using the catalog.
Use multiple database with same recovery catalog

/////////Create database using DBCA for recovery catalog with name as example rman....

/////Create tablespace for recovery catalog 

SQL>  create tablespace rman_catalog

           datafile '/opt/oracle/oradata/rman/rman01.dbf'

           size 10M autoextend on

           extent management local uniform size 1M

////Create user to maintain recovery catalog

 SQL> create user rman identified by rman

            default tablespace rman_catalog

            quota unlimited on rman_catalog;

///To give roles,privilige to rman user.

SQL>grant connect, resource,recovery_catalog_owner to rman;

///Connect with target database and recovery catalog 

Target database=orcl

Recovery Catalog database=rman

$ rman target sys/orcl@orcl catalog rman/rman@rman

RMAN> create catalog tablespace rman_catalog;

////Register the database to use with recovery catalog

RMAN> register database;

Verify and check using rman command 

RMAN> report schema;

report schema,list backup  etc........... on rman prompt.

Another way on recovery catalog database 

$ export ORACLE_SID=rman

SQL>conn rman/rman

SQL> select * from rc_database;

Here show database information target database with dbid