Thursday, March 27, 2014

SCRIPT FOR RMAN BACKUP

Must be set control file autobackup on

//full incremental level 0  backup

DELETE OBSOLETE;
DELETE EXPIRED ARCHIVELOG ALL;
crosscheck archivelog all;
run
{
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
allocate channel c1 type disk
maxpiecesize=500m;
backup incremental level 0 database tag='weekly_level0';
backup archivelog from time 'sysdate-1' until time 'sysdate';
release channel c1;
}

backup current controlfile;
backup spfile;


//////////////full backup specified directory


run {
     allocate channel ch1 type Disk maxpiecesize = 1900M;
     backup full database noexclude
     include current controlfile
     format '/opt/oracle/backup/datafile_%s_%p.bak'
     tag 'datafile_daily';
   }

run {
     allocate channel ch1 type Disk maxpiecesize = 1900M;
     backup archivelog all
     format '/opt/oracle/backup/archivelog_%s_%p.bak'
     tag 'archivelog_daily';
   }

run {
   allocate channel ch1 type Disk maxpiecesize = 1900M;
   backup format '/opt/oracle/backup/controlfile_%s.bak'
   current controlfile;
   }

ORACLE EXAM DUMP PAPER FOR CERTIFICATION

Check following link to access the oracle exam dump SQL,OCA,OCP on Oracle 10g

Copy and paste on browser


https://drive.google.com/folderview?id=0ByQqSfeaD8_tN3dPSno0SlVoN3M&usp=drive_web

Monday, March 17, 2014

HAPPY HOLI 2014 TO ALL




Happy Holi 

Thursday, March 13, 2014

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;


Tuesday, March 11, 2014

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.





Wednesday, March 5, 2014

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'


Sunday, March 2, 2014

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