How to Uninstall Oracle 11g Client on Windows

Hi guys i will show you how simple it is to Uninstall Oracle Client on Windows.

1.  Go to Start menu, Programs, Oracle_Client11g_Home and Click it will take you to
      Universal Installer Menu as shown below.

2.  When you Click on Universal Installer it will open this welcome screen, Now click on  Deinstall Products.

3.  When you click in Deinstall Products it will show you these  HOMES. now check OraClient11g_home1and click on remove. 

4. When you click on remove it will tell you run the Deinstall command from this given location. 

5.  Now go manually to this location in D:\app\moin\product\11.2.0\client_1\deinstall and run as 

     administrator.

6.  When you run this deinstall utility Oracle Starts the " Deconfig Tool " which will deconfig 

     the Oracle Client just type ' Y ' and Press Enter.

     

7.  When you type 'Y' it will start Deinstall process and shows 'Oracle Universal Installer Cleanup was successful' which means Installer has deleted all the binaries of this client software.

8.  After Cleanup finished sucessfully Installer will delete all the Binaries from this location as shown below.

9. Now go to your Location where you installed your Client_Home and delete this Client_1 folder manually which contain many sub folders inside.

Happy Learning...

How to Install Oracle11g Client on Windows

Hi Guys its very simple and easy to install Oracle Client.

1. Go to the software location and right click setup file and select 'Run as Administrator'.

2. Select Administrator Type.

3. By default the language will be select as English just click Next..

4. Select location where you want to install the Oracle Client Software.

5.  Client Installer performing Prerequisite Checks requires for this installation.

6. Client Installer displays summary like how much space it requires, Home Location etc..

   click on Finish.

   

7. Installing the Product wait upto 100% it shows.

8.  Installation Finished Successfully click on Close.

9. Now Cross Check from command prompt whether Client has successfully Installed.

>  Put the TNS entry of  database which you want to connect in client_home network/amdin directory.

> You can see my another post on my blog for Uninstalling Oracle Client on Windows using OUI.

Happy Learning...

   

Auditing and its Features in Oracle Database

> Auditing is a method to record the activity of a user. from Oracle 11g auditing is turned on           by default.

> Auditing is controlled be initilization parametre AUDIT_TRAIL and by default its set to DB      which means the audit trail records are stored in the SYSTEM tablespace in the SYS.AUD$         table.

> If you set to OS means it will send the audit trail records to an operating system file                    location =audit_file_dest.

> It's possible to audit both successful and failed activities.

> It is advisable to copy the AUD$ table into a separate tablespace from other SYS-owned              objects. In some cases, the AUD$ table should be owned by a user other than SYS. There are       three reasons for these two statements.

1. If auditing is used, may grow to a very large size, depending on the udit options selected.           Since the table will grow and shrink and be high activity, it is advisable to move it from the           SYSTEM tablespace to avoid fragmentation.

2. Auditing uses more resources and there will be impact on performance and also it depends           largely on the type of auditing you enabled.

3. Setting high level auditing such as connection activity will not have much performance impact as auditing on all SQL statements.

> Auditing can only be performed for users connected directly to the database not to actions for       remote database.

When to Enable Auditing :-

1. Excessive deadlocks are encountered.

2. Records are being read, deleted, or changed without authorization.

Parameters :-

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------- ----------- ---------------------------------------
audit_file_dest                      string      D:\APP\ORACLE\ADMIN\TESTDB\ADUMP
audit_sys_operations           boolean    FALSE
audit_trail                             string       DB


> Audit_file_dest  = Specifies the OS directory where the audit trail is written, when the                AUDIT_TRAIL initialization parameter is set to os.

> Audit_sys_operations  = AUDIT_SYS_OPERATIONS enables or disables the auditing of          operations issued by user SYS, and users connecting with SYSDBA or SYSOPER
    privileges. The audit records are written to the operating system's audit trail. The audit records     will be written in XML format if the AUDIT_TRAIL initialization parameter is set to XML.

> Audit_trail = By default here you have 'DB' option from 11g which means audit trail records      in database. if you keep 'OS' then audit trail files record at os level in Adump.


                       >>>>>>>>  Some Examples of Auditing on DB level <<<<<<<<<

                                         >>>  Object Level Auditing  <<<

# In this example i am enabling audit on SAM user who owned emp table. whenever sam do insert,update and delete it will be audited as shown below. 


SQL> audit select,insert,update,delete on sam.emp by access;
Audit succeeded.

SQL> insert into emp select * from emp;
128 rows created.

SQL> update emp set empno=009 where empno=7788;
8 rows updated.

SQL> delete from emp where empno='7499';
8 rows deleted.

SQL> sho user
USER is "SAM"

SQL> conn / as sysdba
Connected.
Note :-
# We can observe here in below query all the audit_trails are storing in sys.aud$ table which is in system tablespace and audit_trail is default set to "DB" from which we are getting this audit outputs.

SQL> select username,obj_name,action_name from dba_audit_trail where owner='SAM' order by timestamp;

USERNAME      OBJ_NAME        ACTION_NAME
------------ ------------ --------------------------------------
SAM                         EMP                  INSERT
SAM                         EMP                  INSERT
SAM                         EMP                  INSERT
SAM                         EMP                  UPDATE
SAM                         EMP                  UPDATE
SAM                         EMP                  DELETE
SAM                         EMP                  DELETE
SAM                         EMP                  DELETE
9 rows selected.


>> >>>>>  Privilege Level Auditing  <<<<<<<<<<<<

SQL> audit create table by sam;
Audit succeeded.

SQL> create table sam.emp
     (
      empno number(5),
      ename varchar2(5),
      job varchar2(5)
     );

Table created.

SQL> select username,obj_name,action_name from dba_audit_trail where owner='SAM' order by timestamp;

USERNAME    OBJ_NAME       ACTION_NAME
------------ ------------ --------------------------------------
SAM                      EMP               CREATE TABLE

1 rows selected.

     >>>>> AUDIT  ENABLE WITH OS OPTION  <<<<<<

SQL> ALTER System set audit_trail=os scope=spfile;

SQL> Shut immediate

SQL> Startup

SQL> show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\APP\ORACLE\ADMIN\TESTDB\ADUMP
audit_sys_operations           boolean    FALSE
audit_trail                             string       OS

> Now we will create one table to generate the audit trails at OS level.

#  In Windows audit trails are recorded in Windows Event Viewer.

SQL> audit create table by sam;
Audit succeeded.

SQL> create table sam.deptnew as select * from scott.emp;
Table created.

I Opened the Event Viewer and Check Whether trails are generating or not.

* You can seee in Action[52] audit trail is generated, sam user has created deptnew table. 

> Audit trails generates in OS or DB it will consume space.
   you have option to clear the logs and increase the size of Event viewer logs.

1. Right Click the event logs which you want to increase the size.

2. If you want to clear all the logs just click on Clear Logs....


MANAGING SYS.AUD$ :-

> If connections are being audited and the audit trail table (SYS.AUD$) is full, users will not be allowed to log into the database.

> If the audit trail record cannot be written, the audited action will not be executed and errors will be generated. The DBA can CONNECT INTERNAL using Server Manager in either GUI or command-line mode and clean out the audit trail.

> The maximum size allowed for an audit trail written to the database is determined at the time the database is created.By default, the size reflects the system tablespace default values. 

> The sql.bsq script,which is executed when the database is created, sets the size of the         SYS.AUD$ table.


> We should develop a strategy to cleaned up Audit-Trails on regular or weekily basis from    sys.aud$.


PURGING AUDIT RECORDS FORM SYS.AUD$
=====================================

Disabling Audit:-

Noaudit Table;
Noaudit All Privileges;

Turn Off All Auditing:-

Noaudit All;
Noaudit All Privileges;
Noaudit All On Default;

Purge Auditing:-

Delete From Sys.Aud$;
Truncate From Sys.Aud$

Delete From Sys.Aud$ Where Obj$Name='Emp';

Scenario on Deleting SYS.AUD$

> Take the backup of sys.aud$ table before deleting using exp utility.
> In 11G we cannot run expdp to export sys.aud table we have to execute exp option rather than expdp.

C:\Users\oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 16:13:54 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(*) from sys.aud$;
  COUNT(*)
------------
        50
SQL> EXIT

C:\Users\oracle\audit_exp> exp sytem/manager file=audit_exp tables=sys.aud$

Export: Release 11.2.0.3.0 - Production on Thu Mar 24 11:53:26 2016

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


EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: system
Password:

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

About to export specified tables via Conventional Path ...
Current user changed to SYS

. . exporting table                           AUD$         49 rows exported

Export terminated successfully without warnings.

C:\Users\oracle>sqlplus / as sysdba

SQL> noaudit all;
Noaudit succeeded.

SQL> truncate table sys.aud$;

Table truncated.

SQL> select count(*) from sys.aud$;

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


Happy Learning...

How to Count Particular User in Oracle

You can see the output of this Queries. We need to type the usernames whose names are starting from Example : ACC


> Accountants = 77

> Engineers      = 402

> Managers      =   44


SQL> select count(*) from dba_users where username like 'ACC%';

  COUNT(*)
----------
        77

SQL> select count(*) from dba_users where username like 'ENGR%';

  COUNT(*)
----------
       402

SQL> select count(*) from dba_users where username like 'MGR%';

  COUNT(*)
----------
        44


Happy Learning...

ASMCMD IN ORACLE WINDOWS 2008 Server

>> ASMCMD  is a command line utility for managing files and directories in ASM diskgroups.

>> It offers Unix-style filesystem commands for managing ASM directories and files.
     Prior to Oracle 10gR2,ASM had to be administered via OEM or SQL statements while connected to a               special ASM instance.

>> ASMCMD cannot be used to create or drop diskgroups, or to add or drop disks in a disk group.
   These ASM administrative tasks must be accomplished via SQL commands.


Invoking ASMCMD


> The asmcmd utility is located in ORACLE_HOME/bin.


D:\app\oracle\product\11.2.0\dbhome_1\BIN> dir asmcmd.bat
 Volume in drive D has no label.
 Volume Serial Number is E2B9-6A23

 Directory of D:\app\oracle\product\11.2.0\dbhome_1\BIN

04/28/2010  09:20 PM             2,809   asmcmd.bat
               1 File(s)          2,809 bytes
               0 Dir(s)  48,266,641,408 bytes free

> We start asmcmd by setting ORACLE_SID to the ASM instance.


C:\Users\oracle>set ORACLE_HOME=D:\app\oracle\product\11.2.0\dbhome_1

C:\Users\oracle>set oracle_sid=+asm ( For Single Instance)

C:\Users\oracle>set oracle_sid=+ASM1  (For RAC use ASM1 ,ASM2 depends upon your node)

C:\Users\oracle>sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 16 13:52:59 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
+asm1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

C:\Users\oracle>asmcmd
ASMCMD>

Some Examples for asmcmd :-

ASMCMD> lsdg

State                  Type        Rebal   Sector   Block     AU      Total_MB     Free_MB   Req_mir_free_MB  Usable_file_MB  Offline_disks Voting_files  Name

MOUNTED       EXTERN      N         512     4096    1048576    511994     380349                 0                380349                 0             N         DATA/
MOUNTED       EXTERN      N         512     4096    1048576    102397     102002                 0                102002                 0             Y       OCRVOTE/
MOUNTED       EXTERN      N         512     4096    1048576    511997     496080                 0                496080                 0             N         RECO/


ASMCMD> ls
DATA/
OCRVOTE/
RECO/

ASMCMD> ls -l
State                        Type         Rebal       Name
MOUNTED             EXTERN        N          DATA/
MOUNTED             EXTERN        N         OCRVOTE/
MOUNTED             EXTERN        N         RECO/

ASMCMD> du
Used_MB      Mirror_used_MB
 147607              147607

ASMCMD> lsct

DB_Name           Status             Software_Version  Compatible_version  Instance_Name  Disk_Group

+ASM           CONNECTED        11.2.0.3.0               11.2.0.3.0                    +asm1               DATA
+ASM           CONNECTED        11.2.0.3.0               11.2.0.3.0                    +asm1            OCRVOTE
hrlive            CONNECTED        11.2.0.3.0               11.2.0.0.0                     hrlive1              DATA
hrlive            CONNECTED        11.2.0.3.0               11.2.0.0.0                     hrlive1              RECO
IECLIVE       CONNECTED        11.2.0.3.0               11.2.0.0.0                     ieclive1            DATA
IECLIVE       CONNECTED        11.2.0.3.0               11.2.0.0.0                     ieclive1            RECO


ASMCMD> lsdsk
Path
\\.\ORCLDISKDATA0
\\.\ORCLDISKDATA1
\\.\ORCLDISKOCRVOTE0
\\.\ORCLDISKRECO0


>> You can use the "?" or "help" command to display all asmcmd commands:


ASMCMD> ?

      Commands  :-
   

        md_backup, md_restore

        lsattr, setattr

        cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias
        mkdir, pwd, rm, rmalias

        chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount
        offline, online, rebal, remap, umount

        dsget, dsset, lsop, shutdown, spbackup, spcopy, spget
        spmove, spset, startup

        chtmpl, lstmpl, mktmpl, rmtmpl

        chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr
        mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr

        volcreate, voldelete, voldisable, volenable, volinfo
        volresize, volset, volstat


ASMCMD Commands With Description :-


cd                  :  Change directory
cp                  :  Copy file
du                  :  Disk space used by a directory and its subdirectories
exit                :  Exit the utility
find                :  Locate the path for all occurrences of the specified filename
help               :  Displays Command Assistance
ls                   :  List the contents of a directory
lsct                :  List info about ASM clients
lsdg               :  List all disk groups and their attributes
lsdsk              : List all physical disks visible to ASM
md_backup    : Create a backup of the mounted diskgroups
md_restore    :  Restore the diskgroups from a backup.
mkalias          :  Create an alias for a system generated filename.
mkdir             :  Create directory
PWD              :  Print Working Directory
remap            :   Repair a range of blocks on a disk
rm                  :  Remove the specified files or directories
rmalias           :  Remove specified alias


Happy Learning ...

ORA-03113 end-of-file on communication channel

>> When our developers are trying to connect my test database its showing ORA-03113 end of file                        communication channel.

>>  The connection between Client and Server process was broken this shoot up ORA-03113 error.


C:\Users\oracle> set oracle_sid= testdb

C:\Users\oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 15 12:35:41 2016

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

Connected to an idle instance.


SQL> startup

ORACLE instance started.

Total System Global Area        1.0689E+10 bytes
Fixed Size                                 2264616 bytes
Variable Size                            6543114712 bytes
Database Buffers                      4127195136 bytes
Redo Buffers                            16900096 bytes
Database mounted.
ORA-03113  : end-of-file on communication channel
Process ID    : 5114
Session ID    : 191 Serial number: 3


>> In my Alert log and Trace files following errors have shown.

 Alert log :-

ORA-03113 : end-of-file on communication channel
Process ID   : 5114
Session ID   : 191 Serial number: 3


Trace File :-

ARC1: Error 19809 Creating archive log file to 'K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2016_03_10\O1_MF_1_58_%U_.ARC'

ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.


>> The problem you can see above in trace file, my FRA destination is filled up.

>> So we have to delete the archivelogs and make space to generate new archives.



SQL> startup mount;

ORACLE instance started.

Total System Global Area      1.0689E+10 bytes
Fixed Size                              2264616 bytes
Variable Size                         6543114712 bytes
Database Buffers                   4127195136 bytes
Redo Buffers                         16900096 bytes
Database mounted.

Connect From RMAN and Delete Archivelogs

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\oracle> rman

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 10 14:09:03 2016

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

RMAN> connect target \

target database Password:
target database Password:
connected to target database: TESTDB (DBID=2750453233, not open)


RMAN> delete expired archivelog all;


Do you really want to delete the above objects (enter YES or NO)? yes

deleted archived log

archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_08\O1_MF_1_72_BFGQZ4OX_.ARC RECID=3 STAMP=871142933
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_08\O1_MF_1_73_BFGQZ5H1_.ARC RECID=4 STAMP=871142934
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_08\O1_MF_1_74_BFGQZ2HP_.ARC RECID=1 STAMP=871142931
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_08\O1_MF_1_75_BFGQZ3G5_.ARC RECID=2 STAMP=871142932
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_08\O1_MF_1_1_BFGS7WV5_.ARC RECID=5 STAMP=871144238
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_10\O1_MF_1_2_BFLYTWYN_.ARC RECID=6 STAMP=871281038
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_11\O1_MF_1_3_BFP3LWPN_.ARC RECID=7 STAMP=871384205
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_12\O1_MF_1_4_BFRMVYL4_.ARC RECID=8 STAMP=871466415
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_14\O1_MF_1_5_BFXTY7N4_.ARC RECID=9 STAMP=871637496
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_16\O1_MF_1_6_BG3HYLRN_.ARC RECID=10 STAMP=871822851
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_17\O1_MF_1_7_BG6GW86G_.ARC RECID=11 STAMP=871920057
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_19\O1_MF_1_8_BGC61B7J_.ARC RECID=12 STAMP=872074843
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_20\O1_MF_1_9_BGGM0ZWN_.ARC RECID=13 STAMP=872186449
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_22\O1_MF_1_10_BGM4TB15_.ARC RECID=14 STAMP=872335738
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_23\O1_MF_1_11_BGP3GMHR_.ARC RECID=15 STAMP=872432644
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_25\O1_MF_1_12_BGT00Z5D_.ARC RECID=16 STAMP=872560208
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_26\O1_MF_1_13_BGXH5M8O_.ARC RECID=17 STAMP=872674021
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_02_28\O1_MF_1_14_BH2R01T6_.ARC RECID=18 STAMP=872846899
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_01\O1_MF_1_15_BH5O9YKH_.ARC RECID=19 STAMP=873201648
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_02\O1_MF_1_16_BH8C5LOX_.ARC RECID=20 STAMP=873289571
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_02\O1_MF_1_17_BH8QBX5F_.ARC RECID=21 STAMP=873302029
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_03\O1_MF_1_18_BHB1Y3RY_.ARC RECID=22 STAMP=873345652
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_04\O1_MF_1_19_BHF7WYSZ_.ARC RECID=23 STAMP=873450064
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_05\O1_MF_1_20_BHJVZ2KQ_.ARC RECID=24 STAMP=873568915
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_07\O1_MF_1_21_BHOPQ5T0_.ARC RECID=25 STAMP=873727382
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_09\O1_MF_1_22_BHTLB1L6_.ARC RECID=26 STAMP=873886706
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_10\O1_MF_1_23_BHWZBKHN_.ARC RECID=27 STAMP=873965570
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_11\O1_MF_1_24_BJ0X4WBC_.ARC RECID=28 STAMP=874094413
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_13\O1_MF_1_25_BJ5KV4GX_.ARC RECID=29 STAMP=874246677
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_14\O1_MF_1_26_BJ8D86DC_.ARC RECID=30 STAMP=874339255
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_16\O1_MF_1_27_BJDKT4DD_.ARC RECID=31 STAMP=874476021
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_17\O1_MF_1_28_BJJ3J4FF_.ARC RECID=32 STAMP=874592437
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_19\O1_MF_1_29_BJNOJZL0_.ARC RECID=33 STAMP=874741968
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_20\O1_MF_1_30_BJRR274X_.ARC RECID=34 STAMP=874875640
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_21\O1_MF_1_31_BJV8Y567_.ARC RECID=35 STAMP=874958454
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_23\O1_MF_1_32_BJYLZ38C_.ARC RECID=36 STAMP=875067028
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_24\O1_MF_1_33_BK1X9QQC_.ARC RECID=37 STAMP=875175913
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_26\O1_MF_1_34_BK76467O_.ARC RECID=38 STAMP=875348791
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_27\O1_MF_1_35_BKBZNT57_.ARC RECID=39 STAMP=875473227
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_28\O1_MF_1_36_BKG2QBM5_.ARC RECID=40 STAMP=875574683
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_30\O1_MF_1_37_BKKX5L28_.ARC RECID=41 STAMP=875700067
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_03_31\O1_MF_1_38_BKO54KQP_.ARC RECID=42 STAMP=875806530
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_02\O1_MF_1_39_BKSZNJ01_.ARC RECID=43 STAMP=875964736
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_03\O1_MF_1_40_BKXBRCTF_.ARC RECID=44 STAMP=876074429
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_04\O1_MF_1_41_BL02N7D2_.ARC RECID=45 STAMP=876164408
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_06\O1_MF_1_42_BL44VKT1_.ARC RECID=46 STAMP=876297762
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_07\O1_MF_1_43_BL7BZHMO_.ARC RECID=47 STAMP=876402336
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_09\O1_MF_1_44_BLCQ9JCR_.ARC RECID=48 STAMP=876546017
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_10\O1_MF_1_45_BLHMGVGP_.ARC RECID=49 STAMP=876673164
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_11\O1_MF_1_46_BLL7689Y_.ARC RECID=50 STAMP=876758905
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_12\O1_MF_1_47_BLO5ON0Y_.ARC RECID=51 STAMP=876855653
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_14\O1_MF_1_48_BLS89M5B_.ARC RECID=52 STAMP=876989412
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_16\O1_MF_1_49_BLYK43P8_.ARC RECID=53 STAMP=877162293
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_17\O1_MF_1_50_BM2OSCWF_.ARC RECID=54 STAMP=877298140
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_19\O1_MF_1_51_BM5W12Y4_.ARC RECID=55 STAMP=877402835
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_20\O1_MF_1_52_BM994D7J_.ARC RECID=56 STAMP=877514557
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_21\O1_MF_1_53_BMDH71VL_.ARC RECID=57 STAMP=877619091
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_22\O1_MF_1_54_BMHNY7JJ_.ARC RECID=58 STAMP=877723256
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_24\O1_MF_1_55_BMN42Q9M_.ARC RECID=59 STAMP=877869832
deleted archived log
archived log file name=K:\FAST_RECOVERY_AREA\TESTDB\TESTDB\ARCHIVELOG\2015_04_25\O1_MF_1_56_BMQT88FO_.ARC RECID=60 STAMP=877990841

Deleted 60 objects

RMAN> exit

Recovery Manager complete.

C:\Users\oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 10 14:21:29 2016

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

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


SQL> startup

ORACLE instance started.

Total System Global Area        1.0689E+10 bytes
Fixed Size                                 2264616 bytes
Variable Size                            6543114712 bytes
Database Buffers                      4127195136 bytes
Redo Buffers                            16900096 bytes
Database mounted.
Database opened.


Happy Learning... 

Mining Particular Records of a table in Oracle Using Logminer.

We will create one scenario which MINES the DELETED records from Archive-Logs using Logminer Utility.


>> Check total records before deleting emp table.

SQL> select count(*) from emp;

  COUNT(*)
----------------
      6144

>> Now delete the records from emp table.
>> 2048 records deleted form the emp table of SCOTT schema.


SQL> delete from emp where ename='SCOTT';

2048 rows deleted.

>> NOw check the total records after deleting.

SQL> select count(*) from emp;

  COUNT(*)
------------
      4096


> In your productdion enviorment Developer will give you date and time when rows are deleted.

 Lets Start Logminer Scenario 

>>  Create any directory at os level, I created as 'DEMO1' which will use by the                                                           DBMS_LOGMNR_D.BUILD procedure which generate SQL statements.


C:\Users\oracle> mkdir demo1

C:\Users\oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 29 09:44:46 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


>> Map the above 'DEMO1' directory at database level by setting initialization parameter                                         'utl_file_dir' and bounce the database.


SQL> alter system set utl_file_dir='C:\Users\oracle\demo1' scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area      8351150080 bytes
Fixed Size                               2271832 bytes
Variable Size                           1577059752 bytes
Database Buffers                      6761218048 bytes
Redo Buffers                            10600448 bytes
Database mounted.
Database opened.


>> Oracle stores information requires at the time of recovery. So you need additional information to                         analyze redo log files by adding supplemental log data.


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.


>> Run this pl/sql procudeure because Logminer engine uses data dictionary which will extracted in this file        'DEMO1' which you created.


SQL> exec dbms_logmnr_d.build('dfile','C:\Users\oracle\demo1');

PL/SQL procedure successfully completed.


> We deleted the rows at 10:26am at that time the sequence number of logfile is 1575,1576 & 1577 as shown        in this query output.


SQL> select  to_char(first_time,'dd-mon-yy hh24:mi:ss'),sequence#,thread# from v$log_history;


TO_CHAR(FIRST_TIME               SEQUENCE#   THREAD#
------------------ ---------- --------------------------------------------
07-mar-16 11:00:40                          1565                 1
07-mar-16 11:01:40                          1566                 1
07-mar-16 11:42:48                          1567                 1
07-mar-16 11:42:51                          1568                 1
08-mar-16 15:27:54                          1569                 1
09-mar-16 10:10:04                          1570                 1
09-mar-16 17:01:43                          1571                 1
12-mar-16 19:12:52                          1572                 1
12-mar-16 19:37:57                          1573                 1
12-mar-16 19:38:42                          1574                 1
13-mar-16 10:24:28                          1575                 1
13-mar-16 11:30:33                          1576                 1
13-mar-16 11:55:14                          1577                 1


>> you can observe the time from 10:24 to 11:55 (3) ARCHIVES are generated with different                             sequence number

1. O1_MF_ 1 _ 1575_CGHDVX _.ARC
2. O1_MF_ 1 _ 1576_CG7IDVX _.ARC
3. O1_MF_ 1 _ 1577_CG8jEVX _.ARC

>> Apply these archive logfiles to start the Mining processes.


SQL> exec dbms_logmnr.add_logfile('K:\fast_recovery_area\otstest\ARCHIVELOG\2016_03_13\O1_MF_ 1 _              1575_CGHDVX _.ARC');

PL/SQL procedure successfully completed.


SQL> exec dbms_logmnr.add_logfile('K:\fast_recovery_area\otstest\ARCHIVELOG\2016_03_13\O1_MF_ 1 _              1576_CG7IDVX _.ARC');

PL/SQL procedure successfully completed.


SQL> exec dbms_logmnr.add_logfile('K:\fast_recovery_area\otstest\ARCHIVELOG\2016_03_13\O1_MF_ 1 _             157_CG8jEVX _.ARC');

PL/SQL procedure successfully completed.



>> Start Logminer now, oracle will pool the Archvied logfiles provided through add_logfile in                 v$logmnr_contents view.


SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'C:\Users\oracle\demo1\dfile');


>> Now Query the deleted records which is populated in v$logmnr_contents view.


SQL> select username,table_name,sql_redo from v$logmnr_contents where seg_name='EMP';

USERNAME       TABLE_NAME              SQL_REDO        
--------------------------------------------------------------------------------------------------------------------------
SCOTT                          EMP                                            
                                                           delete from "SCOTT"."EMP" where "EMPNO" = '7844' and                                                                                     "ENAME" = 'TURNER' and "JOB" = 'SALESMAN'                                                                                                    and "MGR" = '7698' and "HIREDATE" =                                                                                                                TO_DATE('08-SEP-81', 'DD-MON-RR') and                                                                                                            "SAL" = '1500' and "COMM" = '0' and "DEPTNO"                                                                                                 ='30' and ROWID='AAASZHAAEAAAACXAAJ';                                                                
                                                                         
 
SCOTT                          EMP                    delete from "SCOTT"."EMP" where "EMPNO" = '7499'                                                                                             and "ENAME" = 'ALLEN' and "JOB"
                                                                  = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" =                                                                                       TO_DATE('20-FEB-81', 'DD-MON-RR') and                                                                                                             "SAL" = '1600' and "COMM" = '                                                                                                                               300' and   "DEPTNO" = '30' and                                                                                                                               ROWID = 'AAASZAAACXAAB';

>> You can observe here how scott has deleted records, Sorry Guys i cant show you all the 2048                            records because of space issue thats why i pick only 2 records.

>> Now spool this complete query output in notepad and handover to developers they  will analyze                         Constraints,Data-types,indexes and then they will insert back these records in the table.



Happy Learning....