FlashBackup Table Level Posted by MOHAMMED ABDUL HAMEED on 8:39 AM

Drop Table relies on recyclebin.

SQL> select * from tab;

TNAME                  TABTYPE       CLUSTERID
------------------------------------------------------------------
CITY TABLE
INFO TABLE

SQL> drop table city;
Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME,OPERATION from USER_RECYCLEBIN;

OBJECT_NAME                ORIGINAL_NAME OPERATION
---------------------------------------------------------------------------------------------------------
BIN$SvRkQnfIYM3gUwEAAH9==$0         CITY     DROP

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
---------------------------------------------------------------------------------------------
BIN$SvRkQnfIYM3gUwEAAH9==$0
INFO

SQL> flashback table city to before drop;

Flashback Complete.

SQL>select OBJECT_NAME,ORIGINAL_NAME,OPERATION from USER_RECYCLEBIN;

no rows selected

SQL>select * from tab;

TNAME                  TABTYPE       CLUSTERID
------------------------------------------------------------------
CITY TABLE
INFO TABLE

# If we drop the table with the purge option then it cannot be retrive

SQL>drop table city purge;

Table dropped.

SQL>select OBJECT_NAME,ORIGINAL_NAME,OPERATION from USER_RECYCLEBIN;

no rows selected

SQL>select * from tab;

TNAME                  TABTYPE       CLUSTERID
-------------------------------------------------------------
INFO TABLE

Flashback Transaction Query Posted by MOHAMMED ABDUL HAMEED on 8:36 AM

It is used to get the historical data of the given transaction along with sql code to undo the changes made to the particular rows.

It basically provide undo statements of sql queries.

In order to unable UNDO_SQL we need add the supplemental log data.

SQL> alter database add supplemental log data;
Database altered.

SQL> select * from tab;

TNAME                  TABTYPE       CLUSTERID
------------------------------------------------------------------
CITY TABLE

SQL> select * from city;

ID NAME
----------------------------
1 India
2 Telangana
3 Pune

SQL> update city set name='Hyderabad' where id=1;

1 Row Updated.

commit;

SQL> select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana
3 Pune

SQL> select operation,undo_sql
from flashback_transaction_query
where TABLE_NAME='CITY';

OPERATION         UNDO_SQL
---------------------------------------------------------------------------
update update "HAMEED"."CITY" set "NAME"='INDIA' where ROWID='AAAS4zAAARESIK54TGFD/AAA';

SQL>select * from city;

SQL> select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana
3 Pune

SQL> delete from city where id=3;
commit;

SQL>select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana

SQL>select operation,undo_sql
from flashback_transaction_query
where TABLE_NAME='CITY';

OPERATION         UNDO_SQL
---------------------------------------------------------------------------
UPDATE update "HAMEED"."CITY" set "NAME"='INDIA' where ROWID='AAAS4zAAARESIK54TGFD/AAA';

DELETE insert into "HAMEED"."CITY"("ID","NAME") values ('3','pune');

SQL>select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana

SQL>insert into "HAMEED"."CITY"("ID","NAME") values ('3','pune');
1 row created.
commit;

SQL>select * from city;

ID NAME
----------------------------
1 Hyderabad
2 Telangana
3 Pune

Flashback Version Query Posted by MOHAMMED ABDUL HAMEED on 8:33 AM

If we want to find out all the changes on the particular row ( Row Level ) then we can use the Flash Back Version Query.

SQL> show user;
USER is "HAMEED"

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CITY                           TABLE

SQL> select * from city;

ID        NAME
-----     --------
1 Hyderabad
2 Bangalore
3 Pune

SQL> update city set name='Deccan' where id=1;

commit;

SQL> update city set name='Telangana' where id=1;

commit;

SQL> update city set name='India' where id=1;

commit;

SQL> select * from city;

ID        NAME
-----     --------
1 India
2 Bangalore
3 Pune

SQL> select versions_xid as xid,versions_startscn as start_scn,
          versions_endscn as end_scn,versions_operation as Operation,name 
          from city
          versions between scn minvalue and maxvalue
          where id=1;

   XID START_SCN END_SCN OPERATION NAME
-----------------------------------------------------------------------------------------------------------------
0B00040903000        346491                                           U India
0F00070040000        346479 346491                U Telangana
0E00050070000        346468               346479                 U                 Deccan
0D00020008000        341737                346468                 I                  Hyderabad

DML operations : U = Update,I= Insert,D=Delete

Present : 

SQL> select * from emp where id=1;

ID        NAME
-----     --------
1 India

FlashBack Query Posted by MOHAMMED ABDUL HAMEED on 8:00 AM

FlashBack Query :

It is used to retrive the data in past , point in time at Query Level.

SQL> show user;
USER is "HAMEED"

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
INFO                           TABLE

SQL> set time on

20:57:11 SQL> select * from info;

ID      NAME
------  ----------
1            A

2           B

3           C


20:57:19 SQL> select sysdate from dual;

SYSDATE
---------
31-DEC-16

21:01:22 SQL> update info set name='AAA' where id='1';

1 row updated.

21:01:49 SQL> commit;

Commit complete.

21:03:23 SQL> select * from info;

ID      NAME
-----------------------
1        AAA

2          B

3          C

21:03:34 SQL> select * from info as of timestamp to_timestamp('31-DEC-16 21:01:22', 'dd-mon-rr hh24:mi:ss') where id=1;

ID     NAME
-------------------
1          A

21:04:05 SQL> update info set name=(select name from info as of timestamp to_timestamp ('31-DEC-16 21:01:22', 'dd-mon-rr hh24:mi:ss') where id=1) where id=1; 


1 row updated.

21:05:15 SQL> select * from info;

ID      NAME
-----------------------
1          A

2          B

3          C

21:16:53 SQL> commit;

Commit complete.

FlashBack Technology Posted by MOHAMMED ABDUL HAMEED on 7:56 AM

FlashBack Technology :

Its an Oracle Powerful Feature ,Which helps us to perform various tasks such as :

1.Quickly recovery from Logical error. 
2.A Database Time Machine which allow you to snake time into the past time.
3.Can be used to fix only in Logical Corruption not in Physical Corruption.

How to Enable/Disable Flashback in Oracle Database Posted by MOHAMMED ABDUL HAMEED on 6:49 AM

1.Database has to be in Archivelog mode

      SQL> select log_mode from v$database;

               LOG_MODE
                ------------
NOARCHIVELOG

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

    SQL> startup mount;
ORACLE instance started.

Total System Global Area 1573527552 bytes
Fixed Size                  1345184 bytes
Variable Size             436210016 bytes
Database Buffers         1124073472 bytes
Redo Buffers               11898880 bytes
Database mounted.

    SQL> alter database archivelog;

Database altered.

   SQL> alter database open;

Database altered.

    SQL> archive log list

         Database log mode              Archive Mode
Automatic archival                Enabled
Archive destination                                                                              USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence        22
Next log sequence to archive      24
Current log sequence                 24

  SQL> select log_mode from v$database;

              LOG_MODE
               ------------
             ARCHIVELOG

2. Configure Flash Recovery Area.

3.To enable or disable flashback , we can change this while database is in open mode.


     SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

   SQL> select flashback_on from v$database;

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

   SQL> alter database flashback on;

           Database altered.

  SQL> select flashback_on from v$database;

           FLASHBACK_ON
            ------------------
                YES

  SQL> alter database flashback off;

           Database altered.

  SQL> select flashback_on from v$database;

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

DELETE - TRUNCATE - DROP Posted by MOHAMMED ABDUL HAMEED on 1:44 AM

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

SQL> SELECT COUNT(*) FROM info;

  COUNT(*)
----------
        7

SQL> DELETE FROM info WHERE job = 'HYD';

1 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM info;

  COUNT(*)
----------
        6

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

SQL> TRUNCATE TABLE info;

Table truncated.

SQL> SELECT COUNT(*) FROM info;

  COUNT(*)
----------
         0

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

SQL> DROP TABLE info;

Table dropped.

SQL> SELECT * FROM info;
SELECT * FROM info
              *
ERROR at line 1:
ORA-00942: table or view does not exist

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

Oracle Startup failed with ORA-00119 and ORA-00130 in Linux Posted by MOHAMMED ABDUL HAMEED on 12:00 PM

ORA-00119: invalid specification for system parameter LOCAL_LISTENER and ORA-00130

Oracle Startup failed with ORA-00119 and ORA-00130.

No value set for the LOCAL_LISTENER parameter.

Then check the hostname for the system.

Then Try to find the IP of the system

Their will be difference in between '/etc/hosts' and '/etc/sysconfig/network' hostname specifications , Correct it and it will work.

Register service name with Oracle listener Posted by MOHAMMED ABDUL HAMEED on 11:28 AM

OraTab File Posted by MOHAMMED ABDUL HAMEED on 1:05 PM

OraTab File :

"Oratab" is a file created by Oracle in the /etc or /var/opt/oracle directory when installing database software. 

It is created by root.sh and updated by the Database Configuration Assistant when creating a database.

A colon, ':', is used as the field terminator.  A new line terminates the entry.  Lines beginning with a pound sign, '#', are comments.

Entries are of the form:
                $ORACLE_SID:$ORACLE_HOME::


[oracle@prim etc]$ pwd
/etc
[oracle@prim etc]$ cat oratab 
#

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
hyd:/u01/app/oracle/oracle11g:N
new:/u01/app/oracle/oracle11g:N

Locations Posted by MOHAMMED ABDUL HAMEED on 12:54 PM

Alert log :

This file is where database-level errors are written and operations such as startup, shutdown, and other events are logged. 

Oracle writes to this text-based file in a chronological order when the database is running.

Oracle will automatically create a new alert log file whenever the old one is deleted.

Usage :

Whenever a problem occurs, review the alert log file.

Review the alert log file daily (if you’re the Database Administrator – DBA) to ensure errors are not occurring undetected.

Location :

Oracle will write the alert.log file to the directory as specified by the BACKGROUND_DUMP_DEST parameter. 

If this parameter is not set, the alert.log will be created in a directory below the value of the DIAGNOSTIC_DEST parameter: 

DIAGNOSTIC_DEST/diag/rdbms/DB_NAME/ORACLE_SID/trace. 

If this later parameter is not set, the alert.log file is created in the ORACLE_HOME/rdbms/trace directory.

SQL> show parameter BACKGROUND_DUMP_DEST

NAME                                    TYPE        VALUE
----------------------------- -----------    ------------------------------
background_dump_dest  string      /u01/app/oracle/diag/rdbms/oracl/trace

ORA-01940: Cannot drop a user that is currently connected. Posted by MOHAMMED ABDUL HAMEED on 11:43 AM

Step 1.Find the Connected Session , user specific

SQL>Select 'alter system kill session ''' || sid || ',' || serial# || ''' IMMEDIATE ;' from v$session where username = 'your_user_name';

Step 2 : Then Kill the Sessions.
    
SQL>Alter system kill session '86,2' IMMEDIATE;

Step 3: Drop user

SQL>Drop user your_user_name cascade;

Oracle Client 11g Installation On Windows Posted by MOHAMMED ABDUL HAMEED on 8:58 AM

Undo or Rollback Posted by MOHAMMED ABDUL HAMEED on 8:37 AM

Undo or Rollback is the backbone of the read consistency.Mainly be helpful in Flash Back Technology.

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. 

SQL> show parameter UNDO_RETENTION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

SQL> SELECT SUM(a.bytes)/1024/1024"UNDO_SIZE_IN_MB"
FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

UNDO_SIZE_IN_MB
    ---------------
             70

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;  2  

UNDO_BLOCK_PER_SEC
------------------
        2.52166667

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';  

DB_BLOCK_SIZE [KByte]
---------------------
                 8192

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 ROUND((d.undo_size / (to_number(f.value) *
 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
 FROM (
 SELECT SUM(a.bytes) undo_size
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#
 ) d,
 v$parameter e,
 v$parameter f,
 (
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec
 FROM v$undostat
 ) g
WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size'; 

ACTUAL UNDO SIZE [MByte]       UNDO RETENTION [Sec]      OPTIMAL UNDO RETENTION [Sec]
   ------------------------                             -------------------------             ----------------------------
                  70                                               900                                       3553

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

ACTUAL UNDO SIZE [MByte]     UNDO RETENTION [Sec]      NEEDED UNDO SIZE [MByte]
     ------------------------                     -------------------------                 ------------------------
                      70                                     900                                      17.7304688

How to Uninstall Oracle Database 11g from Windows Posted by MOHAMMED ABDUL HAMEED on 2:17 AM

Steps: 

Stop all Oracle services first.

START -> RUN -> Services.msc ( Locate all Ora* services & click on STOP )

Start -> RUN -> Regedit

Locate HKEY_LOCAL_MACHINE folder in the registry
Open Software folder
Delete the Oracle folder under Software
Open System folder in HKEY_LOCAL_MACHINE
Open CurrentControlSet folder within the System folder
Open Services
Delete all keys related to Oracle. Every key starts with “ORA..”
Close the regedit
Delete ORACLE_HOME folder
Delete the Oracle folder in Program Files

Reboot

Include Select statement in EXPDP Posted by MOHAMMED ABDUL HAMEED on 3:38 PM

Create a parameter file with all the details as follows :

DIRECTORY = DATA_PUMP_DIR
DUMPFILE = hyd11.dmp
LOGFILE = hyd_lg.log

include=TABLE:"IN (select table_name from dba_tables where table_name like 'NAM%')"

 include=table:\"in\(select table_name from all_tables where table_name like \'NAM%\')\"
                               

Now Export it by using the below syntax of export :

[oracle@stdby ~]$ expdp PARFILE='/u01/hyd.par'

Export: Release 11.2.0.3.0 - Production on Sat Nov 26 05:00:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: hameed
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HAMEED"."SYS_EXPORT_SCHEMA_01":  hameed/******** PARFILE=/home/oracle/Desktop/hyd.par 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "HAMEED"."NAME"                             5.828 KB       1 rows
. . exported "HAMEED"."NAMEALL"                          5.828 KB       1 rows
. . exported "HAMEED"."NAMEFEW"                          5.828 KB       1 rows
Master table "HAMEED"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HAMEED.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/hyd/dpdump/hyd11.dmp

Job "HAMEED"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:00:57

Export / Import by using Parameter File Posted by MOHAMMED ABDUL HAMEED on 2:34 PM

Export :

Create a parameter file with all the details as follows :

DIRECTORY = DATA_PUMP_DIR
DUMPFILE = hyd.dmp
LOGFILE = hyd_lg.log
TABLES = hameed.info

Now Export it by using the below syntax of export :

[oracle@stndby ~]$ expdp PARFILE='/u01/hyd.par'

Export: Release 11.2.0.3.0 - Production on Sat Nov 26 03:31:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA PARFILE=/home/oracle/Desktop/hyd.par 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "HAMEED"."INFO"                             5.828 KB       1 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/hyd/dpdump/hyd.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 03:32:04

Import :

Create a parameter file with all the details as follows :

DIRECTORY = DATA_PUMP_DIR
DUMPFILE = hyd.dmp
LOGFILE = hyd_lg.log
TABLES = hameed.info

Now Import it by using the below syntax of import :

[oracle@localhost ~]$ impdp PARFILE=/home/oracle/Desktop/hyd.par

Import: Release 11.2.0.3.0 - Production on Sat Nov 26 04:02:31 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA PARFILE=/home/oracle/Desktop/hyd.par 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HAMEED"."INFO"                             5.828 KB       1 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 04:02:38

Oracle Statistics Posted by MOHAMMED ABDUL HAMEED on 12:36 PM

When a valid SQL statement is fired , the oracle database have to decide its plan in order to retrieve the necessary data requested by the query.

This decision can be made by the using the following methods mention below :

Rule Based Optimizer (RBO) : Optimization is done via a set of rules,distribution of data is not taken into account.

Cost Based Optimizer (CBO) : Execution plan is calculated by taking into account the distribution of data. Requires analysis of tables and indexes (DBMS_STATS)

Create Database using DBCA - Oracle 11g Posted by MOHAMMED ABDUL HAMEED on 1:07 PM