Saturday, 28 December 2013

kkjcre1p: unable to spawn jobq slave process,TNS-12514,TNS-12523,TNS-12502,TNS-12520,TNS-12528

This problem occurred due to resource allocation on the db side .


Here is the solution approach :-



1.check the kernel parameter and see if it configured at optimum level.
2.Check the swap space and their usage and re-config them if it is needed .
3.It may be your JOB_QUEUE_PROCESSES is too low comparison to processes parameter , increase it upto  
  32 and decrease the  processes parameter upto 800 for now .
4.Check the session,transaction and process parameter and tune the session and process parameter.
5.monitor the PROCESSES usage at the database level using the V$RESOURCE_LIMIT view and increase it if is
   needed .


Sunday, 1 December 2013

oracle export partition wise from one database to another database

[oracle@linux251 ~]$ export ORACLE_SID=dwh02
[oracle@linux251 ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 11:27:07 2013

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> alter tablespace TEST_TABLESPACE_2 read only;

Tablespace altered.

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
[oracle@linux251 ~]$ expdp  transportable=always directory=bkp dumpfile=part_part.dmp tables=SCOTT.PART_PART:P0032,SCOTT.PART_PART:P0033,SCOTT.PART_PART:P0034,SCOTT.PART_PART:P0035,SCOTT.PART_PART:P0036,SCOTT.PART_PART:P0037,SCOTT.PART_PART:P0038,SCOTT.PART_PART:P0039,SCOTT.PART_PART:P0040,SCOTT.PART_PART:P0041,SCOTT.PART_PART:P0042,SCOTT.PART_PART:P0043,SCOTT.PART_PART:P0044,SCOTT.PART_PART:P0045,SCOTT.PART_PART:P0046,SCOTT.PART_PART:P0047,SCOTT.PART_PART:P0048,SCOTT.PART_PART:P0049,SCOTT.PART_PART:P0050,SCOTT.PART_PART:P0051,SCOTT.PART_PART:P0052,SCOTT.PART_PART:P0053,SCOTT.PART_PART:P0054,SCOTT.PART_PART:P0055,SCOTT.PART_PART:P0056,SCOTT.PART_PART:P0057,SCOTT.PART_PART:P0058,SCOTT.PART_PART:P0059,SCOTT.PART_PART:P0060,SCOTT.PART_PART:P0061,SCOTT.PART_PART:P0062,SCOTT.PART_PART:P0063,SCOTT.PART_PART:P0064,SCOTT.PART_PART:P0065,SCOTT.PART_PART:P0066,SCOTT.PART_PART:P0067,SCOTT.PART_PART:P0068,SCOTT.PART_PART:P0069,SCOTT.PART_PART:P0070

Export: Release 11.2.0.3.0 - Production on Fri Nov 29 11:28:27 2013

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

Username: sys as sysdba
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 "SYS"."SYS_EXPORT_TABLE_01":  sys/******** AS SYSDBA transportable=always directory=bkp dumpfile=part_part.dmp tables=SCOTT.PART_PART:P0032,SCOTT.PART_PART:P0033,SCOTT.PART_PART:P0034,SCOTT.PART_PART:P0035,SCOTT.PART_PART:P0036,SCOTT.PART_PART:P0037,SCOTT.PART_PART:P0038,SCOTT.PART_PART:P0039,SCOTT.PART_PART:P0040,SCOTT.PART_PART:P0041,SCOTT.PART_PART:P0042,SCOTT.PART_PART:P0043,SCOTT.PART_PART:P0044,SCOTT.PART_PART:P0045,SCOTT.PART_PART:P0046,SCOTT.PART_PART:P0047,SCOTT.PART_PART:P0048,SCOTT.PART_PART:P0049,SCOTT.PART_PART:P0050,SCOTT.PART_PART:P0051,SCOTT.PART_PART:P0052,SCOTT.PART_PART:P0053,SCOTT.PART_PART:P0054,SCOTT.PART_PART:P0055,SCOTT.PART_PART:P0056,SCOTT.PART_PART:P0057,SCOTT.PART_PART:P0058,SCOTT.PART_PART:P0059,SCOTT.PART_PART:P0060,SCOTT.PART_PART:P0061,SCOTT.PART_PART:P0062,SCOTT.PART_PART:P0063,SCOTT.PART_PART:P0064,SCOTT.PART_PART:P0065,SCOTT.PART_PART:P0066,SCOTT.PART_PART:P0067,SCOTT.PART_PART:P0068,SCOTT.PART_PART:P0069,SCOTT.PART_PART:P0070
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/part_part.dmp
******************************************************************************
Datafiles required for transportable tablespace TEST_TABLESPACE_2:
  /data/dwh02/datafiles/test_tablespace_2.dbf
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 11:28:45

[oracle@linux251 ~]$ cp /data/dwh02/datafiles/test_tablespace_2.dbf /data/dwh04/dwh04/test_tablespace_2.dbf
[oracle@linux251 ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 11:28:59 2013

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> alter tablespace TEST_TABLESPACE_2 read write;

Tablespace altered.

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
[oracle@linux251 ~]$ export ORACLE_SID=dwh04
[oracle@linux251 ~]$ impdp  PARTITION_OPTIONS=departition TRANSPORT_DATAFILES='/data/dwh04/dwh04/test_tablespace_2.dbf' DIRECTORY=bkp DUMPFILE=part_part.dmp

Import: Release 11.2.0.3.0 - Production on Fri Nov 29 11:30:03 2013

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

Username: sys as sysdba
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
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  sys/******** AS SYSDBA PARTITION_OPTIONS=departition TRANSPORT_DATAFILES=/data/dwh04/dwh04/test_tablespace_2.dbf DIRECTORY=bkp DUMPFILE=part_part.dmp
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:30:23

[oracle@linux251 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 11:32:31 2013

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 * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
PART_PART_P0032                TABLE
PART_PART_P0033                TABLE
PART_PART_P0034                TABLE
PART_PART_P0035                TABLE
PART_PART_P0036                TABLE
PART_PART_P0037                TABLE
PART_PART_P0038                TABLE
PART_PART_P0039                TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PART_PART_P0040                TABLE
PART_PART_P0041                TABLE
PART_PART_P0042                TABLE
PART_PART_P0043                TABLE
PART_PART_P0044                TABLE
PART_PART_P0045                TABLE
PART_PART_P0046                TABLE
PART_PART_P0047                TABLE
PART_PART_P0048                TABLE
PART_PART_P0049                TABLE
PART_PART_P0050                TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PART_PART_P0051                TABLE
PART_PART_P0052                TABLE
PART_PART_P0053                TABLE
PART_PART_P0054                TABLE
PART_PART_P0055                TABLE
PART_PART_P0056                TABLE
PART_PART_P0057                TABLE
PART_PART_P0058                TABLE
PART_PART_P0059                TABLE
PART_PART_P0060                TABLE
PART_PART_P0061                TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PART_PART_P0062                TABLE
PART_PART_P0063                TABLE
PART_PART_P0064                TABLE
PART_PART_P0065                TABLE
PART_PART_P0066                TABLE
PART_PART_P0067                TABLE
PART_PART_P0068                TABLE
PART_PART_P0069                TABLE
PART_PART_P0070                TABLE
SALGRADE                       TABLE

43 rows selected.