How to Check Oracle Database Listener Port Status from Sql & OS Level

Hi Guy's,

You can check listener status in windows environment by using Sql & Os using Netstat.


SQL> select * from gv_$listener_network;

  

C:\Users\oracle> netstat -na |find "1521"

  TCP    0.0.0.0:1521                0.0.0.0:0                       LISTENING
  TCP    10.10.20.244:1521      10.10.20.244:49928     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49929     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49933     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49934     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49939     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49947     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:50037     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.246:50605     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:50613     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:55062     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57671     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57675     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57680     ESTABLISHED



C:\Users\oracle> netstat -a -o -n

Active Connections

  Proto  Local Address    Foreign Address       State           PID
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       752
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:1158           0.0.0.0:0              LISTENING       2828
  TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       1984
  TCP    0.0.0.0:1831           0.0.0.0:0              LISTENING       14504
  TCP    0.0.0.0:3389           0.0.0.0:0              LISTENING       5316


Happy Learning..

Gathering Table Stats in Oracle11g

Hi Guy's,

• When we gather stats we are updating Optimizer to choose optimal execution plan which enhance the performance of the database.
• We can gather stats at database level,schema level, Table level,Index level.

Example for Table Level Stats :

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'SCOTT',
         tabname =>'EMPLOYEE',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /


Parameters:-

•  Owner-name = Schema Name which holds the table.
• Tabname = Table Name
• Degree  = When you set degree to some value oracle will invoke slave process which speedup the performance.
• cascade = The cascade option can be used to analyze al the indexes.
• METHOD_OPT (FOR COLUMNS SIZE AUTO) = Auto is the Oracle default and this option will analyze histograms  on what the optimizer considers to be "important columns". The optimizer does this by examining your current workload when you run the dbms_stats command,interrogating all current SQL in the library cache to see which columns might benefit from a histogram.
•  estimate_percent = This argument is a new way to allow Oracle's dbms_stats to automatically   estimate  the "best" percentage of a segment to sample when gathering statistics.

Happy Learning...

How To Check Redolog File Size in Oracle

SQL> set linesize 300
SQL> column REDOLOG_FILE_NAME format a45
SQL> SELECT
        a.GROUP#,
        a.THREAD#,
        a.SEQUENCE#,
        a.ARCHIVED,
        a.STATUS,
        b.MEMBER    AS REDOLOG_FILE_NAME,
        (a.BYTES/1024/1024) AS SIZE_MB
    FROM v$log a
    JOIN v$logfile b ON a.Group#=b.Group#
    ORDER BY a.GROUP# ASC;

    GROUP#    THREAD#  SEQUENCE#  ARC           STATUS                    REDOLOG_FILE_NAME                    SIZE_MB
---------- ---------- ---------- --- --------- --------------------------------------------- ---------------------------------------------------------------------
         1               1               17309           NO             CURRENT   +RECO/ieclive/onlinelog/group_1.257.860629021        200
         1               1               17309           NO              CURRENT   +DATA/ieclive/onlinelog/group_1.266.860629021       200
         2               1               17306          YES             INACTIVE  +RECO/ieclive/onlinelog/group_2.259.860629023        200
         2               1               17306          YES             INACTIVE  +DATA/ieclive/onlinelog/group_2.268.860629023        200
         3               2               30917          NO              CURRENT   +DATA/ieclive/onlinelog/group_3.261.860629087        200
         3               2               30917          NO              CURRENT   +RECO/ieclive/onlinelog/group_3.261.860629087        200
         4               2               30914         YES              INACTIVE  +DATA/ieclive/onlinelog/group_4.270.860629089         200
         4               2               30914         YES              INACTIVE  +RECO/ieclive/onlinelog/group_4.262.860629089         200
         5               1               17307         YES              INACTIVE  +RECO/ieclive/onlinelog/group_5.258.860629023         200
         5               1               17307         YES              INACTIVE  +DATA/ieclive/onlinelog/group_5.272.860629023         200
   

Enjoy Reading...

How To Check Undo Tablespace Size and Free Size in Oracle

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
     from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a,
     dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = 'UNDO'
     group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
     from DBA_UNDO_EXTENTS c where status <> 'EXPIRED'group by c.tablespace_name) b
     where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME     SIZEMB    USAGEMB        FREEMB
--------------- ---------- ---------- -------------------------------------------------
UNDOTBS1                        15360         50.6875           15309.3125
UNDOTBS2                        15360         40.5625           15319.4375

Enjoy Reading...

How to Exclude Constraints in oracle using Datapump Expdp Utility

D:\app\MOIN\product\11.2.0\dbhome_1\BIN>expdp dumpfile=PRSEMP_EXCONST directory=full_imp tables=fmis.prs_master_employee_main EXCLUDE=CONSTRAINT

Export: Release 11.2.0.3.0 - Production on Tue Nov 22 23:25:22 2016

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

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
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=PRSEMP_EXCONST directory=full_imp tables=fmis.prs_master_employee_main EXCLUDE=CONSTRAINT
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "FMIS"."PRS_MASTER_EMPLOYEE_MAIN"           333.6 KB    1508 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded


Happy Learning....

Load Excel Data in Oracle Tables Using SQL-Loader

Hi Guys,

Today I will show you how simple is to load data in oracle table from Excel using 'SQL-LOADER' utility.

Steps:-

> First convert the Excel file into (.CSV) format.
> Table Structure like column names and Data types values in Excel sheet Should be same in the Database.
> Create Controlfile in notepad and mention parameters like..

LOAD DATA
INFILE 'C:\Users\Desktop\EMP1.csv'
INTO TABLE EMP1
FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY ","
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)


> Now run the sqlldr utility with username/password and controlfile (.ctl) path.
> Check the row count after you get 'COMMIT POINT REACHED' message.
> We have loaded 10 records from excel Sheet which displays the output 'logical record 10'



D:\app\oracle> sqlldr sam/s control=C:\Users\Desktop\load.ctl log=emp1.log
SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 4 10:33:40 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 10


> Now check the table counts.

SQL> select count(*) from emp1;

  COUNT(*)
-----------
        10


Happy Learning...

Oracle Background Process Status

SQL> select sid,  process, program from v$session s join v$bgprocess using (paddr) where s.status = 'ACTIVE' and rownum <20;

       SID     PROCESS                  PROGRAM
-----------------------------------------------------------------------
         1         3248                     ORACLE.EXE (DIA0)
         2         3280                     ORACLE.EXE (MMNL)
        32        3252                     ORACLE.EXE (MMAN)
        63        3220                     ORACLE.EXE (PMON)
        64        3256                     ORACLE.EXE (DBW0)
        94        3224                     ORACLE.EXE (PSP0)
        95        3260                     ORACLE.EXE (LGWR)
        98        3408                     ORACLE.EXE (QMNC)
       125       3232                     ORACLE.EXE (VKTM)
       126       3264                     ORACLE.EXE (CKPT)
       157       3236                     ORACLE.EXE (GEN0)
       158       3268                     ORACLE.EXE (SMON)
       187       3240                     ORACLE.EXE (DIAG)
       188       3272                     ORACLE.EXE (RECO)
       190       5816                     ORACLE.EXE (SMCO)
       218       3244                     ORACLE.EXE (DBRM)
       219       3276                     ORACLE.EXE (MMON)
       222      4040                     ORACLE.EXE (CJQ0)

18 rows selected.

Happy Learning....

How to Check Partitions Size in Oracle Table

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='SHSHTDTA_NEW_PART';

SUM(BYTES/1024/1024)
---------------------------------
                4528


SQL>  select owner,segment_name,partition_name,segment_type,bytes/1024/1024 "MB" from dba_segments where segment_name='SHSHTDTA_NEW_PART';

OWNER       SEGMENT_NAME       PARTITION_NAME   SEGMENT_TYPE             MB
----------- ------------------ ------------- ---------------- -----------------------------------------------------------
HMIS        SHSHTDTA_NEW_PART      SH_P2011          TABLE PARTITION             8
HMIS        SHSHTDTA_NEW_PART      SH_P2013          TABLE PARTITION             8
HMIS        SHSHTDTA_NEW_PART      SH_P2014          TABLE PARTITION            16
HMIS        SHSHTDTA_NEW_PART      SH_P2015          TABLE PARTITION            56
HMIS        SHSHTDTA_NEW_PART      SH_P2016          TABLE PARTITION           816
HMIS        SHSHTDTA_NEW_PART      SH_P2017          TABLE PARTITION          2112
HMIS        SHSHTDTA_NEW_PART      SH_P2018          TABLE PARTITION          1280
HMIS        SHSHTDTA_NEW_PART      SH_P2019          TABLE PARTITION           232

8 rows selected.


Happy Learning...

How to Drop All the Indexes in Oracle Table

Hi Guy's,

We can drop all the indexes in a single procedure.


SQL> select table_name,index_name,status from dba_indexes where table_name='SHSHTDTA';

TABLE_NAME     INDEX_NAME                     STATUS
---------- --------------------------------------------------------------------
SHSHTDTA         SHSHTDTA_PK                      VALID
SHSHTDTA         IX_PANIC                                VALID
SHSHTDTA         IX_SHDATE                             VALID
SHSHTDTA       SHSHTDTA_INDX                     VALID


SQL>  BEGIN
      FOR ind IN
      (SELECT index_name FROM user_indexes WHERE table_name='SHSHTDTA' AND index_name NOT IN
      (SELECT unique index_name FROM user_constraints WHERE table_name='SHSHTDTA' AND index_name IS          NOT NULL))
      LOOP
      execute immediate 'DROP INDEX '||ind.index_name;
      END LOOP;
      END;
        /

PL/SQL procedure successfully completed.


SQL> select table_name,index_name,status from dba_indexes where table_name='SHSHTDTA';
no rows selected




Happy Reading...

How to Disable All Constraints in Oracle

Hi Guy's,

Today we will see how to disable all the constraints in oracle table.


SQL> select table_name,constraint_name, status from user_constraints where table_name='SHSHTDTA'

TABLE_NAME   CONSTRAINT_NAME       STATUS
--------------- --------------- --------------------------------------------
SHSHTDTA          SYS_C0053639              ENABLED
SHSHTDTA          SYS_C0053640              ENABLED
SHSHTDTA          SYS_C0053641              ENABLED
SHSHTDTA          SYS_C0053642              ENABLED
SHSHTDTA          SYS_C0053643              ENABLED
SHSHTDTA          SYS_C0053644              ENABLED
SHSHTDTA          SYS_C0053645              ENABLED
SHSHTDTA          SYS_C0053646              ENABLED

8 rows selected.



SQL>   BEGIN
       FOR c IN
       (SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE                     c.table_name = t.table_name
       AND c.status = 'ENABLED' AND  t.table_name='EMP' ORDER BY c.constraint_type DESC)
       LOOP
       dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' ||                     c.constraint_name);
       END LOOP;
       END;
    /

PL/SQL procedure successfully completed.




SQL> select constraint_name,status from dba_constraints where table_name='SHSHTDTA';

CONSTRAINT_NAME          STATUS
--------------- --------------------------------------
SYS_C0041058                    DISABLED
SYS_C0041057                    DISABLED
SYS_C0041056                    DISABLED
SYS_C0041055                    DISABLED
SYS_C0041054                    DISABLED
SYS_C0041053                    DISABLED
SYS_C0041052                    DISABLED
SYS_C0041051                    DISABLED
SHSHTDTA_TMP_PK        DISABLED

9 rows selected.



Happy Reading...

How to Delete Duplicate Rows in Oracle

 Hi Guy's,

Today we will see how to eliminate the duplicate records from a table.When tables are designed initially on test environment in some scenarios the primary key is not define, so in these cases there's is a chance of rows duplication.

SQL> select count(*) from table_name;

  COUNT(*)

---------------

   4814821

SQL> select 

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty from schema.table_name group by

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty having count(*)>1;

  

6 rows selected.

 >>>>>>>> We have 6 rows Duplicate here  <<<<<<<<<

  

SQL> delete from schema.table_name where rowid not in (select min(rowid) from schema.table_name group by

kthscd,

kttrnn,

kttype,

ktdrcd,

ktqty);

6 rows deleted.

SQL> select count(*) from table_name

  COUNT(*)

--------------------

   4814815

Happy Reading...

Top Ten Sizes of Tables and Indexes in Oracle

Hi Guy's,

I found very nice scripts from 'DON BURLESON' website which gives the top ten sizes of Tables and Indexes.



SQL> select * from (select owner,segment_name,bytes/1024/1024 meg from dba_segments where segment_type='TABLE' order by bytes/1024/1024 desc) where rownum <= 10;


SQL> select * from (select owner,segment_name,bytes/1024/1024 meg from dba_segments where segment_type='INDEX' order by bytes/1024/1024 desc) where rownum <= 10;




Happy Reading...

Oracle Alert-log and Trace Files Locations

SQL> show parameter background_dump_dest

NAME                                          TYPE                     VALUE
------------------------------------ ----------- ------------------------------------------------------
background_dump_dest                 string      c:\app\Moin\diag\rdbms\kkprod\kkprod\trace


SQL> select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid ||
     nvl2(v$process.traceid, '_' || v$process.traceid, null )||'.trc' "Trace File"
     from v$parameter u_dump cross join v$parameter db_name cross join v$process
     join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest'
     and db_name.name = 'db_name' and v$session.audsid=sys_context('userenv','sessionid');

Trace File
--------------------------------------------------------------------------------
c:\app\Moin\diag\rdbms\kkprod\kkprod\trace/kkprod_ora_4244.trc
c:\app\Moin\diag\rdbms\kkprod\kkprod\trace/kkprod_ora_1280.trc



Thank's For Learning....

ORA-00205 Restoring Controlfile from RMAN When Autobackup of controlfile is On

>> An ORA-00205 error occurs when the system cannot find the Controlfile.
>> This i tested on my test environment i just removed controlfile and startup the database.
>> Autobackup of controlfile is configured in RMAN.


SQL> startup

ORACLE instance started.
Total System Global Area    7.4826E+10 bytes
Fixed Size                            2263008 bytes
Variable Size                       7.3283E+10 bytes
Database Buffers                 1342177280 bytes
Redo Buffers                       199049216 bytes
ORA-00205: error in identifying control file, check alert log for more info


>> Check Autobackup is ON or Not.

RMAN> show all;
RMAN configuration parameters for database with db_unique_name KKPROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\MOIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFKKPROD.ORA'; # default

RMAN> restore controlfile from autobackup;

Starting restore at 22-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
recovery area destination: +DATA1
database name (or database unique name) used for search: KKPROD
channel ORA_DISK_1: AUTOBACKUP +data1/KKPROD/AUTOBACKUP/2018_01_22/s_966079326.377.966079329 found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP +data1/KKPROD/AUTOBACKUP/2018_01_22/s_966079326.377.966079329
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/kkprod/controlfile/current.262.962995555
output file name=+DATA1/kkprod/controlfile/current.256.966094519
Finished restore at 22-JAN-18



>> Now mount the database, Controlfile is read in MOUNT state.

RMAN> sql 'alter database mount';

>> Now start to recover database, Recover is the process of applying the changes to the database till point of failure.
   these changes are recorded in online redolog and archivelogs .


RMAN>  recover database;

Starting recover at 22-JAN-18
Starting implicit crosscheck backup at 22-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
Finished implicit crosscheck backup at 22-JAN-18
Starting implicit crosscheck copy at 22-JAN-18
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 22-JAN-18
searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_22/s_966079326.377.966079329
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_21/s_966011357.381.966011359
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_18/s_965739076.383.965739079
File Name: +data1/KKPROD/AUTOBACKUP/2018_01_02/s_964349888.503.964349893
File Name: +data1/KKPROD/ARCHIVELOG/2018_01_22/thread_1_seq_1.376.966083591
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file +DATA1/kkprod/archivelog/2018_01_22/thread_1_seq_1.376.966083591
archived log for thread 1 with sequence 2 is already on disk as file +DATA/kkprod/onlinelog/group_2.260.963052729
archived log file name=+DATA1/kkprod/archivelog/2018_01_22/thread_1_seq_1.376.966083591 thread=1 sequence=1
archived log file name=+DATA/kkprod/onlinelog/group_2.260.963052729 thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-JAN-18


RMAN> sql 'alter database open resetlogs';


>> Opening database with resetlogs because database will not apply an archivedlog to a
     datafile unless the RESETLOGS SCN and Timestamp matches.

   * Oracle creates new redologs after resetlogs issued and also reset logsequence number to '1'.
   * After resetlogs Oracle updates all current datafiles,redologs and all archivelogs
     with NEW-SCN and Time-stamp.
 

RMAN> exit

SQL> select instance_name,status from v$instance;

       INSTANCE_NAME       STATUS
      ---------------- -----------------------------
           kkprod                          OPEN


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/kkprod/controlfile/current.262.962995555
+DATA1/kkprod/controlfile/current.256.966094519



Enjoy Reading...

ORA-29701,ORA-01078

Hi Guy's,

Our Test server was abnormally shutdown when i restarted my ASM instance its showing ORA-29701,ORA-01078

and it starts working when i restarted the ASM from services and HAS service using 'CRSCTL' in Windows.



C:\Windows\system32>set oracle_sid=+asm

C:\Windows\system32>sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 29 11:34:05 2018
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service



SQL> exit
Disconnected

C:\Windows\system32> crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.

C:\Windows\system32>crsctl start has
CRS-4123: Oracle High Availability Services has been started.


C:\Windows\system32>crs_stat -t

Name                           Type               Target                State               Host
-------------------------------------------------------------------------------------------------
ora.DATA.dg        ora....up.type        ONLINE           ONLINE        moinit-pc
ora.DATA1.dg      ora....up.type        ONLINE           ONLINE        moinit-pc
ora....ER.lsnr        ora....er.type         ONLINE           ONLINE       moinit-pc
ora.asm                 ora.asm.type        ONLINE          ONLINE       moinit-pc
ora.cssd                ora.cssd.type        ONLINE          ONLINE       moinit-pc
ora.evmd              ora.evm.type         ONLINE           ONLINE       moinit-pc
ora.kkprod.db      ora....se.type          ONLINE           ONLINE       moinit-pc
ora.ons                 ora.ons.type           OFFLINE        OFFLINE
ora.test.db            ora....se.type           ONLINE         ONLINE       moinit-pc
ora.tstrd.db           ora....se.type          ONLINE          ONLINE      moinit-pc



C:\Windows\system32> sqlplus / as sysasm

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option


SQL> select instance_name,status from v$instance;

INSTANCE_NAME        STATUS
---------------- --------------------------------
+asm                             STARTED



Happy Learning...

ORA-31626, UDE-31626,39086,ORA-06512,ORA-19815,ORA-03113

#####################################
      ERRORS IN MY ALERT-LOG FILE
#####################################


Errors in file C:\APP\MOIN\diag\rdbms\kkprod\kkprod\trace\kkprod_ora_5720.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 99.02% used, and has 20971520 remaining bytes available.

************************************************************************
You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+DATA1'
ARC0: STARTING ARCH PROCESSES
Mon Jan 01 15:48:40 2018
ARC1 started with pid=26, OS id=2984
Mon Jan 01 15:48:40 2018
ARC2 started with pid=27, OS id=5724
Mon Jan 01 15:48:40 2018
ARC3 started with pid=28, OS id=1764
Errors in file C:\APP\MOIN\diag\rdbms\kkprod\kkprod\trace\kkprod_ora_5720.trc:
ORA-16038: log 3 sequence# 33 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+DATA/kkprod/onlinelog/group_3.259.963052735'
ORA-00312: online log 3 thread 1: 'E:\FRA\KKPROD\ONLINELOG\O1_MF_3_F3GW4JG3_.LOG'
USER (ospid: 5720): terminating the instance due to error 16038
Mon Jan 01 15:48:41 2018
System state dump requested by (instance=1, osid=5720), summary=[abnormal instance termination].
System State dumped to trace file C:\APP\MOIN\diag\rdbms\kkprod\kkprod\trace\kkprod_diag_3272.trc
Dumping diagnostic data in directory=[cdmp_20180101154841], requested by (instance=1, osid=5720), summary=[abnormal instance termination].
ARC1: Archival started
ARC0: Detected ARCH process failure
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Archival disabled due to shutdown: 1092
Shutting down archive processes
Archiving is disabled
ARC0: Detected ARCH process failure
Mon Jan 01 15:48:49 2018
Instance terminated by USER, pid = 5720

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

 Below is the output generated when i exit Datapump job full=y by pressing CTRL+C job was hanging since 2 hrs.....

UDE-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist

ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1


  ASM started sucessfully ....

  When i started Database it shows ORA-03113 


SQL> startup

ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size                  2264616 bytes
Variable Size            9160360408 bytes
Database Buffers         1509949440 bytes
Redo Buffers               16900096 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5132
Session ID: 193 Serial number: 3


C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 1 15:53:09 2018
Copyright (c) 1982, 2011, Oracle.  All rights reserved.


SQL> conn / as sysdba
Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.
Total System Global Area        1.0689E+10 bytes
Fixed Size                                2264616 bytes
Variable Size                            9160360408 bytes
Database Buffers                     1509949440 bytes
Redo Buffers                           16900096 bytes
Database mounted.


SQL> show parameter db_reco

NAME                                       TYPE                    VALUE
--------------------------------------------------------------------------------
db_recovery_file_dest                string                   +DATA1
db_recovery_file_dest_size        big integer 2G


SQL> alter system set db_recovery_file_dest_size = 20G scope=both;

System altered.

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

C:\Windows\system32> rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 1 16:12:21 2018

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

RMAN> connect target /

connected to target database: KKPROD (DBID=1757363720, not open)

RMAN> delete backup of archivelog all;

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885 RECID=12 STAMP=963661885
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963663274.268.963663275 RECID=13 STAMP=963663275
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963664178.275.963664179 RECID=14 STAMP=963664178
Crosschecked 3 objects


RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP    Key     BS    Key     Pc#    Cp#    Status      Device Type      Piece Name
------- ------- --- --- ----------- ----------- ----------------------------------------------------------------------------------------
12      12      1   1   EXPIRED     DISK        +DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885
13      13      1   1   EXPIRED     DISK        +DATA1/kkprod/autobackup/2017_12_25/s_963663274.268.963663275
14      14      1   1   EXPIRED     DISK        +DATA1/kkprod/autobackup/2017_12_25/s_963664178.275.963664179

Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885 RECID=12 STAMP=963661885
deleted backup piece
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963663274.268.963663275 RECID=13 STAMP=963663275
deleted backup piece
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963664178.275.963664179 RECID=14 STAMP=963664178
Deleted 3 EXPIRED objects


C:\Windows\system32> sqlplus / as sysdba

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

SQL> show parameter db_reco

NAME                                         TYPE            VALUE
-------------------------------------------------------------------
db_recovery_file_dest                string             +DATA1
db_recovery_file_dest_size        big integer      20G

SQL> select instance_name,status from v$instance;

INSTANCE_NAME            STATUS
-------------------------------------------------------
kkprod                                MOUNTED

SQL> alter database open;

Database altered.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME              STATUS
-------------------------------------------------
kkprod                                     OPEN


 Now check any previous expdp jobs are running.


SQL> set lines 200
SQL> SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs ORDER BY 1,2;

OWNER_NAME      JOB_NAME            OPERATION  JOB_MODE               STATE        ATTACHED_SESSIONS
------------------------------ ------------------------------ ----------------------------------------------------------------------------
SYSTEM      SYS_EXPORT_FULL_01       EXPORT         FULL                   NOT RUNNING          0
SYSTEM      SYS_IMPORT_FULL_01        IMPORT          FULL                   NOT RUNNING          0


SQL>  drop table  system.SYS_EXPORT_FULL_01;

Table dropped.


SQL> drop table  SYSTEM.SYS_IMPORT_FULL_01;

Table dropped.


>>> INCREASE SGA SIZE <<<<


SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)
--------------
    9.95534897

SQL> select sum(bytes)/1024/1024/1024 " GB" from v$sgastat where name!='free memory';

        GB
----------
1.55478944

SQL>  alter system set sga_max_size=20g scope=spfile;

SQL> alter system set memory_max_target=25g scope=spfile;

SQL>  alter system set memory_target=25g scope=spfile;


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

SQL> startup

ORACLE instance started.
Total System Global Area 2.1379E+10 bytes
Fixed Size                  2264616 bytes
Variable Size            1.9931E+10 bytes
Database Buffers         1409286144 bytes
Redo Buffers               36073472 bytes
Database mounted.
Database opened.


SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;
TOTAL SGA (GB)
--------------
    19.9107056


SQL> select sum(bytes)/1024/1024/1024 " GB" from v$sgastat where name!='free memory';
        GB
----------
1.36548476


SQL> select owner, count(1), object_type from dba_objects group by owner, object_type having owner in
     (select username from dba_users where default_tablespace = 'USERS') and object_type = 'TABLE';

OWNER                   COUNT(1)        OBJECT_TYPE
----------------------------------------------------------------------
HMIS                            1998                  TABLE
SCOTT                            4                     TABLE
ARCHIVEDB                 1                      TABLE
CMIS                            410                    TABLE
IDMS7                         1978                  TABLE



 All the tables restored as soon i increase the size db_recovery_file_dest and SGA size.
And Import Run Successfully.


E:\imp_kkprod>impdp directory=imp_kkprod log=kkprod_impfull full=y dumpfile=IECLIVE_01JAN2018FULL.DMP


Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "HMIS"."SHSHTDTA"                                        3.524 GB   41666374 rows
. . imported "IDMS7"."TBL_HIJRI_CONTROLS"                 2.403 GB   4982267 rows
. . imported "HMIS"."OTOCURNC"                                      1.446 GB   10604454 rows

100 % Executed Successfully.



Happy Learning....

Logical Cumulative Backups in Oracle 11g using Exp Utility

>> Cumulative Export backsup the tables that have changed since the last cumulative or complete Export.

>> To understand more clearly pls check this scenario in which i took 'Complete' backup first and then i
   perform 'Cumulative' backup.

E:\exp_full> exp file=E:\exp_full\newcomplete_bkp log=newcomplete.log full=y inctype=complete

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
EXP-00041: INCTYPE parameter is obsolete
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL                                       0 rows exported
. . exporting table                   DEF$_AQERROR                                     0 rows exported
. . exporting table                  DEF$_CALLDEST                                     0 rows exported
. . exporting table               DEF$_DEFAULTDEST                                 0 rows exported
. . exporting table               DEF$_DESTINATION                                  0 rows exported
. . exporting table                     DEF$_ERROR                                         0 rows exported
. . exporting table                       DEF$_LOB                                            0 rows exported
. . exporting table                    DEF$_ORIGIN                                         0 rows exported
. . exporting table                DEF$_PROPAGATOR                                  0 rows exported
. . exporting table       DEF$_PUSHED_TRANSACTIONS                     0 rows exported
 . exporting table       WWV_FLOW_PICK_PAGE_VIEWS                    5 rows exported
. . exporting table         WWV_FLOW_PLATFORM_PREF                    0 rows exported
. . exporting table        WWV_FLOW_PLATFORM_PREFS                   21 rows exported
. . exporting table    WWV_FLOW_POPUP_LOV_TEMPLATE             10 rows exported
. . exporting table          WWV_FLOW_PREFERENCES$                       0 rows exported
. . exporting table            WWV_FLOW_PROCESSING                          45 rows exported
. . exporting table     WWV_FLOW_PROVISION_COMPANY               0 rows exported
. . exporting table  WWV_FLOW_PROVISION_SERICE_MOD             0 rows exported
. . exporting table      WWV_FLOW_PURGED_SESSIONS$                   0 rows exported
. . exporting table         WWV_FLOW_QB_SAVED_COND                     0 rows exported
. . exporting table         WWV_FLOW_QB_SAVED_JOIN                        0 rows exported
. . exporting table        WWV_FLOW_QB_SAVED_QUERY                    0 rows exported
. . exporting table         WWV_FLOW_QB_SAVED_TABS                      0 rows exported
. . exporting table          WWV_FLOW_QUERY_COLUMN                    18 rows exported
. . exporting table       WWV_FLOW_QUERY_CONDITION                   6 rows exported
. . exporting table      WWV_FLOW_QUERY_DEFINITION                   6 rows exported
. . exporting table          WWV_FLOW_QUERY_OBJECT                       6 rows exported
. . exporting table         WWV_FLOW_RANDOM_IMAGES                    42 rows exported
. . exporting table WWV_FLOW_REGION_CHART_SER_ATTR           0 rows exported
. . exporting table  WWV_FLOW_REGION_REPORT_COLUMN          7903 rows exported
. . exporting table  WWV_FLOW_REGION_REPORT_FILTER               0 rows exported
. . exporting table   WWV_FLOW_REGION_UPD_RPT_COLS              439 rows exported
. . exporting table        WWV_FLOW_REPORT_LAYOUTS                     0 rows exported
. . exporting table WWV_FLOW_REQUEST_VERIFICATIONS             0 rows exported
. . exporting table        WWV_FLOW_REQUIRED_ROLES                     0 rows exported
. . exporting table    WWV_FLOW_RESTRICTED_SCHEMAS              46 rows exported
. . exporting table         WWV_FLOW_ROW_TEMPLATES                    54 rows exported
. . exporting table  WWV_FLOW_WORKSHEET_CATEGORIES          0 rows exported
. . exporting table     WWV_FLOW_WORKSHEET_COLUMNS            721 rows exported
. . exporting table  WWV_FLOW_WORKSHEET_COL_GROUPS         0 rows exported
 . exporting table                        OWBRTPS                                             0 rows exported
. about to export OWBSYS_AUDIT's tables via Conventional Path ...
. about to export DAVID's tables via Conventional Path ...
. . exporting table                            EMP                    0 rows exported
. . exporting table                            INV                     0 rows exported
. about to export ROSY's tables via Conventional Path ...
. . exporting table                            ACC                    0 rows exported
. . exporting table                           DEPT                   4 rows exported
. about to export SARA's tables via Conventional Path ...
. . exporting table                           DEPT                   4 rows exported
. . exporting table                            EMP                  14 rows exported
. . exporting table                       SALGRADE          5 rows exported

. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS              0 rows exported
. . exporting table                           DEPT               4 rows exported
. . exporting table                            EMP               14 rows exported
. . exporting table                         EMPNEW         14 rows exported
. . exporting table                       SALGRADE       5 rows exported
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.


>> All the tables are exported in this above output.


                              ####################################################
                                          CREATED ONE USER AND ADDED 4 TABLES
                               ####################################################


SQL> create table lab.lab_emp as select * from scott.emp;

Table created.

SQL> create table lab.lab_salgrade as select * from scott.salgrade;

Table created.

SQL> create table lab.lab_bonus as select * from scott.bonus;

Table created.

SQL> create table lab.lab_department as select * from scott.dept;

Table created.

SQL> commit;



 Now Take Cumulative Backup


E:\exp_full> exp file=E:\exp_full\labcum_bkp log=labcum.log full=y inctype=cumulative

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
EXP-00041: INCTYPE parameter is obsolete
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD is not supported. The table will not be exported.
. . exporting table        ORDDCM_CT_PRED_OPRD_TMP
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_TMP is not supported. The table will not be exported.
. . exporting table        ORDDCM_CT_PRED_OPRD_WRK
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_WRK is not supported. The table will not be exported.
. . exporting table                    ORDDCM_DOCS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_RPTS is not supported. The table will not be exported.
. about to export OWBSYS's tables via Conventional Path ...
. about to export OWBSYS_AUDIT's tables via Conventional Path ...
. about to export DAVID's tables via Conventional Path ...
. about to export ROSY's tables via Conventional Path ...
. about to export SARA's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...

. about to export LAB's tables via Conventional Path ...
. . exporting table                      LAB_BONUS                        0 rows exported
. . exporting table                 LAB_DEPARTMENT                 4 rows exported
. . exporting table                        LAB_EMP                           14 rows exported
. . exporting table                   LAB_SALGRADE                    5 rows exported

. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting information about dropped objects
. exporting statistics
Export terminated successfully with warnings.


>> You can observe in both this scenarios output, how 'COMPLETE' option is taking all the tables backup which is nothing but full=y and compare this from the 'CUMULATIVE' output which is exported only 4 tables  which we added after complete backup.




Happy Learning....

Remap Schema in Oracle 11g

>> Oracle Remap option will create SCHEMA on the fly if it is not created before importing.


E:\exp_full> expdp system/kkk666 schemas=scott directory=exp_full dumpfile=scott.dmp logfile=scott.log

Export: Release 11.2.0.3.0 - Production on Mon Jan 8 12:11:15 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_04":  system/******** schemas=scott directory=exp_full dumpfile=scott.dmp logfile=scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."EMPNEW"                            8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_04 is:
  E:\EXP_FULL\SCOTT.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully completed at 12:11:40



        ########################################################
                         IMPORT SCHEMA IN ANOTHER DATABASE
        ########################################################



E:\imp_inc> impdp dumpfile=SCOTT.DMP directory=imp_inc remap_schema=scott : scott log=impscott.log

Import: Release 11.2.0.3.0 - Production on Mon Jan 8 12:17:42 2018

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

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=impscott.log" Location: Command Line, Replaced with: "logfile=impscott.log"
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=SCOTT.DMP directory=imp_inc remap_schema=scott:scott logfile=impscott.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                                5.937 KB       4 rows
. . imported "SCOTT"."EMP"                                  8.570 KB      14 rows
. . imported "SCOTT"."EMPNEW"                         8.570 KB      14 rows
. . imported "SCOTT"."SALGRADE"                     5.867 KB       5 rows
. . imported "SCOTT"."BONUS"                             0 KB              0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 12:17:55


Happy Learning...

Incremental Backups Using Exp / Imp Utility in Oracle 11g

Hi Guy's

You can perform all levels of logical backups using (9i-exp) utility in oracle 11g.

>> You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y).
>> Only users who have the role EXP_FULL_DATABASE can run incremental, cumulative, and complete Exports.

Note:- An incremental Export backups only those tables that have changed since the last incremental, cumulative, or complete Export. this is the reason we have to take complete backup first in-order to take incremental backup.


>> inctype=Complete

E:\ test_exp > exp file=E:\test_fullbkp log=test.log full=y inctype=complete

Export: Release 11.2.0.3.0 - Production on Wed Jan 3 11:33:43 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
EXP-00041: INCTYPE parameter is obsolete
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. . exporting table     WWV_MIG_OLB_T_TP_GG_CPDTXT              0 rows exported
. . exporting table  WWV_MIG_OLB_T_TP_GG_CT_TXTSGT          0 rows exported
. . exporting table   WWV_MIG_OLB_T_TP_GG_GRAPHICS            0 rows exported
. . exporting table    WWV_MIG_OLB_T_TP_G_GRAPHICS              0 rows exported
. . exporting table    WWV_MIG_OLB_VISUALATTRIBUTE               0 rows exported
. . exporting table             WWV_MIG_OLB_WINDOW                        0 rows exported
. . exporting table             WWV_MIG_PLSQL_LIBS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_PLSQL_LIBS is not supported. The table will not be exported.
. . exporting table               WWV_MIG_PROJECTS                           0 rows exported
. . exporting table     WWV_MIG_PROJECT_COMPONENTS          0 rows exported
. . exporting table       WWV_MIG_PROJECT_TRIGGERS               0 rows exported
. . exporting table                 WWV_MIG_REPORT                             0 rows exported
. . exporting table         WWV_MIG_RESERVED_WORDS             87 rows exported
. . exporting table            WWV_MIG_REV_APEXAPP             0 rows exported
. . exporting table              WWV_MIG_REV_FORMS               0 rows exported
. . exporting table            WWV_MIG_REV_QUERIES              0 rows exported
. . exporting table            WWV_MIG_REV_REPORTS             0 rows exported
. . exporting table             WWV_MIG_REV_TABLES               0 rows exported
. . exporting table                   WWV_MIG_RPTS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_RPTS is not supported. The table will not be exported.
. . exporting table               WWV_MIG_RPT_DATA                       0 rows exported
. . exporting table            WWV_MIG_RPT_DATASRC                  0 rows exported
. . exporting table        WWV_MIG_RPT_DATASRC_GRP            0 rows exported
. . exporting table     WWV_MIG_RPT_DATASRC_SELECT        0 rows exported
. . exporting table       WWV_MIG_RPT_DATA_SUMMARY        0 rows exported
. . exporting table       WWV_MIG_RPT_GRP_DATAITEM           0 rows exported
. . exporting table  WWV_MIG_RPT_GRP_DATAITEM_DESC    0 rows exported
. . exporting table  WWV_MIG_RPT_GRP_DATAITEM_PRIV     0 rows exported
. . exporting table          WWV_MIG_RPT_GRP_FIELD                 0 rows exported
. . exporting table         WWV_MIG_RPT_GRP_FILTER                0 rows exported
. . exporting table        WWV_MIG_RPT_GRP_FORMULA           0 rows exported
. . exporting table       WWV_MIG_RPT_GRP_ROWDELIM          0 rows exported
. . exporting table        WWV_MIG_RPT_GRP_SUMMARY           0 rows exported
. . exporting table      WWV_MIG_RPT_REPORTPRIVATE            0 rows exported
. . exporting table     WWV_MIG_RPT_XMLTAGTABLEMAP         15 rows exported
. about to export OWBSYS's tables via Conventional Path ...
. . exporting table                        OWBRTPS          0 rows exported
. about to export OWBSYS_AUDIT's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS               0 rows exported
. . exporting table                           DEPT                  4 rows exported
. . exporting table                            EMP                 14 rows exported
. . exporting table                       SALGRADE           5 rows exported

. about to export SAM's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.

>> You can observe in above output there are no tables in SAM's user

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
              ADD 2 TABLES IN USER SAM
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

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

Table created.

SQL> create table sam.dept as select * from scott.dept;

Table created.

SQL> commit;

Commit complete.

SQL> conn sam/s
Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE


>> Now take Inctype=incremental

>> You can see in this below output how oracle take incremental exports of the newly added tables.


E:\test_exp> exp file=E:\test_fullbkp log=test.log full=y inctype=incremental


Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
EXP-00041: INCTYPE parameter is obsolete
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. about to export OUTLN's tables via Conventional Path ...
. about to export ORDDATA's tables via Conventional Path ...
. . exporting table            ORDDCM_CT_PRED_OPRD
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD is not supported. The table will not be exported.
. . exporting table        ORDDCM_CT_PRED_OPRD_TMP
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_TMP is not supported. The table will not be exported.
. . exporting table        ORDDCM_CT_PRED_OPRD_WRK
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_WRK is not supported. The table will not be exported.
. . exporting table                    ORDDCM_DOCS
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS is not supported. The table will not be exported.
. . exporting table                ORDDCM_DOCS_TMP
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS_TMP is not supported. The table will not be exported.
. . exporting table                ORDDCM_DOCS_WRK
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS_WRK is not supported. The table will not be exported.
. . exporting table            ORDDCM_MAPPING_DOCS
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS is not supported. The table will not be exported.
. . exporting table        ORDDCM_MAPPING_DOCS_TMP
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS_TMP is not supported. The table will not be exported.
. . exporting table        ORDDCM_MAPPING_DOCS_WRK
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS_WRK is not supported. The table will not be exported.
. about to export OLAPSYS's tables via Conventional Path ...
. about to export MDDATA's tables via Conventional Path ...
. about to export SPATIAL_WFS_ADMIN_USR's tables via Conventional Path ...
. about to export SPATIAL_CSW_ADMIN_USR's tables via Conventional Path ...
. about to export SYSMAN's tables via Conventional Path ...
. about to export MGMT_VIEW's tables via Conventional Path ...
. about to export FLOWS_FILES's tables via Conventional Path ...
. about to export APEX_PUBLIC_USER's tables via Conventional Path ...
. about to export APEX_030200's tables via Conventional Path ...
. . exporting table                  WWV_MIG_FORMS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_FORMS is not supported. The table will not be exported.
. . exporting table              WWV_MIG_FRM_MENUS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_FRM_MENUS is not supported. The table will not be exported.
. . exporting table                    WWV_MIG_OLB
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_OLB is not supported. The table will not be exported.
. . exporting table             WWV_MIG_PLSQL_LIBS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_PLSQL_LIBS is not supported. The table will not be exported.
. . exporting table                   WWV_MIG_RPTS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_RPTS is not supported. The table will not be exported.
. about to export OWBSYS's tables via Conventional Path ...
. about to export OWBSYS_AUDIT's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...

. about to export SAM's tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported

. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting information about dropped objects
. exporting statistics
Export terminated successfully with warnings.


>> Now Import this Incremental Backups of 2 Tables in Another Database  


>> Create User at Target Machine in which you want to import these 2 incremental backup tables.


E:\imp_kkprod> imp file=E:\imp_kkprod\empdept.dmp fromuser=sam touser=sam

Import: Release 11.2.0.3.0 - Production on Mon Jan 8 13:18:46 2018

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

Username: system
Password:

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

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SAM's objects into SAM

. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported

Import terminated successfully without warnings.



Enjoy Reading....