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.

Tuesday, 19 November 2013

ORA-00600: [2730], [331] after Switchover in New Standby Database

If you found errors  in the log file as:-

Errors in file /data1/test/diag/diag/rdbms/art02/art02/trace/art02_ora_29254.trc  (incident=8785):
ORA-00600: internal error code, arguments: [2730], [331], [1], [4], [110], [110], [512], [512], [], [], [], []
Incident details in: /data1/test/diag/diag/rdbms/art02/art02/incident/incdir_8785/art02_ora_29254_i8785.trc

RCA :- This happens due to Unmatched compatibility setup on the primary and the standby.The value of compatible parameter in primary and standby is different.

Solution :-

1. Change the value of the compatible parameter on standby

SQL> ALTER SYSTEM SET COMPATIBLE= <same as value of primary> SCOPE=SPFILE;

If you are using pfile then edit pfile and change the value of the parameter

*. COMPATIBLE= <same as value of primary>

2. Shutdown and Restart the standby database in mount stage

3. Restart Managed recovery process.

ORA-16191: Primary log shipping client not logged on standby

The error appears in the ALRET log file AS :- 
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files. returning error ORA-16191
ORA-16191 -Primary log shipping client not logged on standby 

Solution :- 
1.Defer the remote archival destination (log_archive_dest_state_n) parameter in primary. In case of RAC defer LOG_ARCHIVE_DEST_STATE_n in all nodes.
2.check parameter REMOTE_PASSWORDFILE is set to either EXCLUSIVE or SHARED on the both database .
3.Delete the old password file and create the new one for both the DBs.In case of RAC shutdown all instance of standby before recreating the password file by below command :-

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5 force=y ignorecase=Y 

4. set the parameter SEC_CASE_SENSITIVE_LOGON=FALSE on both DBs.
5.Enable the remote archival destination (log_archive_dest_state_n) parameter in primary.
6. Log into standby database and stop the recovery as

SQL> alter database recover managed standby database cancel;

7.  Now restart the recovery as

SQL>alter database recover managed standby database disconnect from session ;

8.Perform a log switch on the primary database and check the archive sequence ,archive destination  and alert logfile. 

SQL>select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST;

Monday, 18 November 2013

ORA-01153: an incompatible media recovery is active standby database oracle



ORA-01153: an incompatible media recovery is active

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;  2
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
*
ERROR at line 1:
ORA-01665: control file is not a standby control file


SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             331352224 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             331352224 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;  2
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#
--------- -------- ---------- ---------- ----------
MRP0      N/A               1         26          0

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

no rows selected

SQL> SELECT open_mode,database_role FROM v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

no rows selected

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>  SELECT open_mode,database_role FROM v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

Monday, 4 March 2013

oracle database migration by cold backup


Below are the all steps which should be follow from db prospective. This is done by cold backup of Oracle Database  :-

On  Existing Server :- Check the kernel parameters , memory , Oracle Software version ,Oracle_Base,Oracle_home, these should be same on new server .

1. Login to db as sys as sysdba .
2. select file_name, tablespace_name, status from dba_data_files;
3. select * from v$recover_file;
4. select name from v$controlfile;
5. show parameter control_file
6. Create pfile from spfile;
7. Size of the temp files :-
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
8. Group , members and Size of redo files:-
set linesize 300
column REDOLOG_FILE_NAME format a50
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;


9. Take backup of jobs , db links .
10. Shutdown immediate
11. Cd $ORACLE_HOME/dbs and see the created newly created pfile
12. Copy all the data files , control files , password file , pfile ,listener.ora and tnsnames.ora to new location

On the Production new server :-

1. Edit the newly created pfile according to location and set the location of control file, db_domain, db_recovery_file_dest, db_recovery_file_dest_size, sga_max_size, sga_target according to server need.
2. Mount the database with new pfile.
3. Rename  Data File :
Alter database rename file ‘<old_location>’ to ‘<new_location>’;

For Redo log file:

Alter database rename file ‘<old_location>’ to ‘<new_location>’;

For Temp Table space temp file :

Alter tablespace temp add tempfile ‘<new_location>’;
4. Open database with command alter database open;
5. Select * from file_name, tablespace_name, status from dba_data_files;
6. select * from v$recover_file;
7. select name from v$controlfile;
8. show parameter control_file
9. create spfile from pfile;
10. shutdown immediate;
11. startup
12. configure listener, tnsnames.ora file
13. restore jobs and dblinks according to others servers

Sunday, 3 February 2013

Oracle PGA Memory Usage ,input output Usage query ,background process


SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait
Time",
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-10
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0
ORDER BY 2 DESC;


select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                                     AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS
            from
              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (12/24);

PGA statistics for each session and background process:-

SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
                  nvl(lower(ssn.machine), ins.host_name) "SESSION",
             to_char(prc.spid, '999999999') "PID/THREAD",
             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",
             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"
    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
          v$instance ins,  v$statname stat1, v$statname stat2
 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session pga memory max'
 AND      se1.sid        = ssn.sid
 AND      se2.sid        = ssn.sid
 AND      ssn.paddr      = bgp.paddr (+)
 AND      ssn.paddr      = prc.addr  (+)
ORDER BY &sort_order DESC;

location of trace file


exec DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);

select value from v$diag_info where name='Default Trace File';

select u_dump.value || '/' || instance.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 instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = 'user_dump_dest'
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');


exec DBMS_MONITOR.SESSION_TRACE_DISABLE;

Saturday, 2 February 2013

Starting background process GTX4 and GLOBAL_TXN_PROCESSES


Starting background process GTX4
Tue Jan 01 15:51:12 2012
GTX4 started with pid=64, OS id=11122
Tue Jan 01 15:51:43 2012
Auto-tuning: Starting background process GTX5
and

Auto-tuning: Shutting down background process GTXi Like messages in alert log in RAC database

This event is recorded in RAC environment only, which is normal. 
 Its normal for the database to automatically tune the number of these processes based on the workload of XA global transactions. If any application which using XA transaction and connecting to oracle RAC database, is it required. This normal behavior and you do not have to worry about this error messages.
 If you are sure that you are not using XA and want to stop these messages. You can disable the GTXn background processes, then you must set GLOBAL_TXN_PROCESSES to 0 in your parameter file. Setting this parameter to 0 will disable the XA support on an Oracle RAC database.
GLOBAL_TXN_PROCESSES specifies the initial number of GTXn background processes (GTX0, ... GTX9 and GTXa, ... GTXj) per instance to support global (XA) transactions in an Oracle RAC environment.

Error ORA-55712 will be returned if you try to run XA transactions on an Oracle RAC database with this parameter set to zer0. You can change the setting to a nonzero value at runtime to turn on the support for XA.
GLOBAL_TXN_PROCESSES is useful for systems that process global (XA) transactions heavily. You do not need to specify a value for this parameter since Oracle Database automatically determines the number of processes and autotunes them, as necessary. GTXn background processes are only seen in an Oracle RAC environment.



Sunday, 20 January 2013

list all stored procedures: 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'


Search package name wise :-
select object_name, object_type , status
     from dba_objects
     where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' )
     and object_name in ('DBMS_JAVA' ,'UTL_DBWS')  ;

OR  generic type search :-


select object_name, object_type , status
     from user_objects
     where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' );
   


SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE
     WHERE Owner IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','APQOSSYS')
     AND Object_Type IN ('PACKAGE', 'TYPE')
     ORDER BY Owner, Object_Type, Object_Name;

SELECT DISTINCT Owner, Object_Type, Object_Name FROM dba_Objects
     WHERE Owner NOT IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','APQOSSYS')
     AND Object_Type IN ('PACKAGE', 'TYPE')
     ORDER BY Owner, Object_Type, Object_Name;

Latch Free wait event



"Latch Free" wait event in this database, to solve this wait event  re-size these parameters using below query.

alter system set open_cursors=600 scope=spfile;
alter system set session_cached_cursors=250 scope=spfile;
alter system set cursor_sharing='FORCE' scope=spfile;
                               
and increase the SGA and PGA accordingly .

We need to bounce DB for these parameters to implement.

last_analyzed Oracle tables


select table_name, last_analyzed, num_rows
from user_tables
where last_analyzed < sysdate - 1
order by 3;


select trunc(last_analyzed) "LAST_ANALYZED",
count(*), decode(db_unique_name,null,name,db_unique_name) DBNAME, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "RUNTIME"
from dba_tables, v$database
where owner='<username>' and last_analyzed >= sysdate-20
group by trunc(last_analyzed), decode(db_unique_name,null,name,db_unique_name)
order by trunc(last_analyzed);

finding last dml on tables in Oracle


There is a way to find the last dml time :-

Alter Table Tabname Monitoring;

Once this is there, Select * from User_Tab_Modifications;

If you want all tables to be Checked, Run the script like this
Spool Monitor.sql
Select 'Alter table '||Table_Name||' Monitoring ;' From User_Tables;
Spool Off;
@Monitor.sql


select to_char(scn_to_timestamp(max(ora_rowscn))) from <table_name>;

 select * from all_tab_modifications where table_name = '<table_name>';

If Oracle Queue is invalid in Oracle Streams


Use this:

conn / as sysdba
exec dbms_aqadm_syscalls.kwqa_3gl_validatequeue('<user_name>','RM_QUEUE_ID11');
commit;
connect <user_name>/&PASSWORD
exec DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => '<queue_name>', force => true);

Monday, 14 January 2013

Invitation for OTN Yathra 2013


Hi

I am proud to inform you that some of the best mind from the field of Oracle like Murali Vallath,Hans Forbrich,Vinay Agrawal,Lucas Jellema,Aman Sharma,Raj Mattamal  are coming to event OTN Yathra 2013 in Delhi on 16th Feb 2013 at Saket  Palace, New Delhi .

This event will be held on 16th February 2013 , from 7:30 AM to 16:00 PM (IST) at  Saket Palace New Delhi and I would like to invite you to participate.

 Please visit www.otnyathra.info and  register yourself .

I have invited a few closes DBAs/Programmer. Welcome to invite an additional team member from your organization who plays a role in DBA/Programming so that they can catch up  to listen from those great minds and latest technology from Oracle.

I will anticipate your visit and greatly appreciate you considering this request.

Regards

Shivendra Narain Nirala

Sunday, 13 January 2013

Oracle Job Backup script


select 'declare lv_job number(10); begin ' ||
       'sys.dbms_job.submit(job => lv_job, what => ''' || what ||
       ''', next_date => (sysdate + 1000), interval => ''' || interval ||
       '''); commit; end;'
  from user_jobs;

job stuck in RAC , Process and locks in Oracle RAC



1. Check for blocking sessions in DB.
2. Check for wait events in the system at time of job execution.
3. Execution plan of sql queries.
4. Stats/Fragmentation in the tables.

 select EVENT,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=75 order by time_waited desc;

 select count(*),event from v$active_session_history where session_id=158 group by event order by count(*) desc;


select PROGRAM,sid,status,last_call_et/(60*60) ,sql_id from gv$session where last_call_et/(60*60) > 3 and status='ACTIVE' AND PROGRAM LIKE '%J00%';


select * from dba_hist_sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$sqltext where sql_id like 'a0du3u2yhkgnd';
select * from gv$active_session_history where session_id = 72 order by sample_id desc;
select * from dba_tab_statistics  where owner like '<user_name>'AND table_name like '<table_name>';
select * from gv$system_event where wait_class !='Idle' order by time_waited desc ;
select * from gv$session where blocking_session is not null;


select sid, process, event, blocking_session, seq# from gv$session where sid=2194;

 SELECT J.JOB|| '|'||V.SID|| '|'||S.SERIAL#|| '|'||S.STATUS|| '|'||LOWNER|| '|'||WHAT|| '|'||J.FAILURES|| '|'|| V.INST_ID
    || '|'||TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400)
    FROM SYS.JOB$ J, GV$LOCK V, GV$SESSION S
    WHERE V.TYPE = 'JQ'
    AND J.JOB = V.ID2
    AND S.SID = V.SID;


SELECT J.JOB|| '|'||V.SID|| '|'||S.SERIAL#|| '|'||S.STATUS|| '|'||LOWNER|| '|'||WHAT|| '|'||J.FAILURES|| '|'|| V.INST_ID
|| '|'||TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400)
FROM SYS.JOB$ J, GV$LOCK V, GV$SESSION S
WHERE V.TYPE like 'CJ%'
AND J.JOB = V.ID2
AND S.SID = V.SID;

select SCHEMA_USER ||','||job||','||WHAT||',' || INTERVAL ||','||BROKEN||','||LAST_DATE||','||round((sysdate-next_date)*1440)||','||NEXT_DATE  from dba_jobs where job in (310,265,266,267,268,645,281,285,286) order by job;

select inst_id,event,time_waited,average_wait,wait_class from (select inst_id,event,time_waited,average_wait,wait_class, 
row_number() over (partition by inst_id order by time_waited desc, average_wait desc) row_num
from gv$system_event 
where wait_class!='Idle' )
where row_num<=10
order by inst_id,row_num;




select chr(to_char(bitand(p1,-16777216))/16777215)||
chr(to_char(bitand(p1, 16711680))/65535) "lock",
to_char( bitand(p1, 65535) )    "mode", sid
from gv$session_wait
where lower(event) = 'dfs lock handle';


SELECT b.name||'|'|| b.value_string
FROM
gv$sql_bind_capture b
WHERE   b.sql_id = '4tsv2myfxsr12';



SELECT event AS "Event|Name",
       total_waits "Total|Waits",
       round(time_waited / 100, 0) "Seconds|Waiting",
       total_timeouts "Total|Timeouts",
       average_wait / 100 "Average|Wait|(in secs)"
  FROM sys.v_$system_event e
  ORDER BY time_waited DESC;

select nvl(s.username,s.program) username, s.sid sid, s.serial# serial, s.sql_hash_value sql_hash_value, 
substr(decode(w.wait_time, 0, w.event, 'ON CPU'),1,15) event , w.p1 p1, w.p2 p2, w.p3 p3 from v$session s, v$session_wait w
where w.sid=s.sid and s.status='ACTIVE'and s.type='USER';


stick the job to the specific instance in Oracle RAC

First method ( and easy one too ) :-
note:-  must perform this activity on each instance one by one.
......stop jobs which is assigning to the specific instance.....

$ export ORACLE_SID=SID

connect database using job owner ....

$ sqlplus username/password

list of jobs and its instance.... 

sql> select JOB,INSTANCE from user_jobs;
       JOB   INSTANCE
---------- ----------
      2510          0
      2951          0
      2992          0
      3091          0
      3111          0
      1570          0
      1567          0
      1568          0
      1569          0
      1571          0
      1572          0

stick the job to the specific instance.....

Syntax
DBMS_JOB.INSTANCE (
   job        IN BINARY_INTEGER,
   instance   IN BINARY_INTEGER,
   force      IN BOOLEAN DEFAULT FALSE);

sql>execute dbms_job.instance(job=>1571, instance=>2,force=>true);

verify from the instance number........

sql>select JOB,INSTANCE from user_jobs;
       JOB   INSTANCE
---------- ----------
      2510          0
      2951          0
      2992          0
      3091          0
      3111          0
      1570          0
      1567          0
      1568          0
      1569          0
      1571          2

sql> exit


....start jobs........

======================================================================

Second Method ( the long one ) :- 

BEGIN
  -- Create a new service associated with the specified TSN service name.
  DBMS_SERVICE.create_service (
    service_name => '<service name>',
    network_name => '<db name>.<domain name>');
 
  -- Start the specified service.
  DBMS_SERVICE.start_service (
    service_name => ' <service name>');
 
  -- Disconnects all sessions associated with the specified service.
  DBMS_SERVICE.disconnect_session (
    service_name => '<service name>');
 
  -- Stop the specified service.
  DBMS_SERVICE.stop_service (
    service_name => '<service name>');
 
  -- Delete the specified service.
  DBMS_SERVICE.delete_service (
    service_name => '<service name>');
END;
/
 
Some examples of using the srvctl utility to do similar actions are listed below.
 
# Create the service on two nodes.
srvctl add service -d <db name> -s <service name> -r <instance name>,<instance name>
 
# Stop and start the service on a single or multiple nodes.
srvctl stop service -d <db name> -s <service name> -i <instance name>,<instance name>
srvctl start service -d <db name> -s <service name> -i <instance name>
 
# Disable and enable the service on a single or multiple nodes.
srvctl disable service -d <db name> -s <service name> -i <instance name>,<instance name>
srvctl enable service -d <db name> -s <service name> -i <instance name>
 
# Display the current status of the service.
srvctl status service -d <db name> -s <service name> -v
 
# Remove the service from both nodes.
srvctl remove service -d <db name> -s <service name> -i <instance name>,<instance name>
 
Once a service is present, it can be assigned to a job class during creation or subsequently using the set_attribute procedure, as shown below.
 
BEGIN
  DBMS_SCHEDULER.create_job_class (
    job_class_name => '<job class name>',
    service        => ‘<service name>’);
 
  DBMS_SCHEDULER.set_attribute (
    name      => '<job class name>',
    attribute => 'instance_stickiness',
    value     => ‘true’);
END;
/
 
The following scenario will explain more specifically how services can be used to partition applications in a three-node RAC environment.
 
For services to function correctly, the Global Services Daemon (GSD) must be running on each node in the cluster.  The GSDs are started using the gsdctl utility, which is part of the Cluster Ready Services (CRS) installation, so they must be started from that environment.
 
# Set environment.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/crs
export PATH=$ORACLE_HOME/bin:$PATH
 
# Start GSD daemon.
gsdctl start
 
Once the GSDs are running, the user must check that the cluster configuration is correct.  The following command and output show the expected configuration for a three-node database called <db name>.
 
srvctl config database -d <db name>
 
This configuration is typically performed during the cluster database creation, but it can be performed subsequently using the following commands.
 
srvctl add database -d <db name> -o /u01/app/oracle/product/11.2.0/db_1

 
Assume that two applications should run in the following way:
OLTP - Should run on nodes one and two of the RAC, but is able to run on node three if nodes one and two are not available.
BATCH - Should run on node three, but is able to run on nodes one and two if node three is not available.
To meet this requirement, the following services can be created:
 
# Set environment.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
 
# Create services.
srvctl add service -d <db name> -s OLTP_SERVICE -r <instance 1>,<instance 2> -a <instance 1>,<instance 2>,<instance 3>
srvctl add service -d <db name> -s BATCH_SERVICE -r <instance 3> -a <instance 1>,<instance 2>,<instance 3>
 
The OLTP_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on nodes one and two, indicated by the -r option.  The BATCH_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on node three, indicated by the -r option.
 
The services can be started and stopped using the following commands:
 
srvctl start service -d <db name> -s OLTP_SERVICE
srvctl start service -d <db name> -s BATCH_SERVICE
 
srvctl stop service -d <db name> -s OLTP_SERVICE
srvctl stop service -d <db name> -s BATCH_SERVICE

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database



Installing DBMS_JAVA package in Oracle following  files need to be run:- 

sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';

@?/javavm/install/initjvm.sql;
@?/xdk/admin/initxml.sql;
@?/xdk/admin/xmlja.sql;
@?/rdbms/admin/catjava.sql;
@?/rdbms/admin/catexf.sql;

and verify your java installation in Oracle Database 

sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';
sql> select dbms_java.longname(object_name), status, object_type from all_objects where
      object_type = 'JAVA CLASS';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

calling UTL_DBWS web services through Oracle Database :- 

set the follwing parameters in .bash_profile of oracle user 
export JAVA_HOME=$ORACLE_HOME/jdk
ORACLE_BASE=/opt/oracle/product; export ORACLE_BASE
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME
PATH=$PATH:/opt/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/bin:$PATH; export PATH
CLASSPATH=.:/opt/oracle/product/11.2.0/dbhome_1/jdbc/lib:$ORACLE_HOME/sqlj/lib/
translator.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:$ORACLE_HOME/sqlj/lib/dbwsa.j
ar:$ORACLE_HOME/javavm/lib/aurora.zip:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORA
CLE_HOME/jdk/lib/dt.jar:$ORACLE_HOME/jdk/lib/tools.jar:$ORACLE_HOME/jlib/jssl
1_1.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORAC
LE_HOME/lib/xsu12.jar:$ORACLE_HOME/jlib/jndi.jar:$ORACLE_HOME/rdbms/jlib/aq
api.jar:$ORACLE_HOME/rdbms/jlib/jmscommon.jar:$ORACLE_HOME/lib/xmlparserv2.
jar; export CLASSPATH
LD_LIBRARY_PATH=/opt/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/lib; export LD_LIBRARY_PATH
PATH=$PATH:/opt/oracle/product/11.2.0/dbhome_1/bin; export PATH

then at sql prompt :- 

show parameter SHARED_POOL_SIZE;
show parameter JAVA_POOL_SIZE ;
show parameter LARGE_POOL_SIZE;


sql>alter system set JAVA_POOL_SIZE=150M scope=both; 
sql>alter system set SHARED_POOL_SIZE=150M scope=both; 
sql>alter system set LARGE_POOL_SIZE=150M scope=both;  


sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%';
In Oracle Database 11g, the UTL_DBWS PL/SQL package is installed in the database SYS schema. To verify the installation, 
try to describe the package as follows:
SQL> describe sys.utl_dbws;

ERROR:
ORA-04043: object sys.utl_dbws does not exist

Cuase : - UTL_DBWS package was not installed in SYS schema by default in Oracle Database 11g R2.

Solution :- Manually install the UTL_DBWS package  in SYS schema by following the steps as below:


install utl_dbws :- If the output indicates that the package is not yet installed,download the latest UTL_DBWS, install them in the 
$ORACLE_HOME/sqlj/lib location  and then run the following scripts under SYS:

download site : - http://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

unzip dbws-callout-utility-10131.zip sqlj/* -d $ORACLE_HOME
unzip dbws-callout-utility-10131.zip samples/* -d $ORACLE_HOME/sqlj
unzip dbws-callout-utility-10131.zip *.htm -d $ORACLE_HOME/sqlj

@$ORACLE_HOME/sqlj/lib/utl_dbws_decl.sql
@$ORACLE_HOME/sqlj/lib/utl_dbws_body.sql

at the sql prompt :- 

sql>SELECT owner, status, count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE='JAVA CLASS' GROUP BY owner, status; 

If there are classes that are listed as being in an 'INVALID' state, you can run the following script to attempt to make these objects VALID: 

$ORACLE_HOME/rdbms/admin/utlrp.sql 

Once this has been run, recheck the status of your Java objects. 

When loading the jar files in the desired schema, make sure the PATH environment variable includes the $ORACLE_HOME/bin directory.  
From a terminal window, run the following command:-

loadjava -u sys/sys -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar 

select owner, object_type, created, status from dba_objects where object_name='UTL_DBWS';

then run the follwoing calls :- sys as sysdba for required user - 


call dbms_java.grant_permission( '<user_name>','SYS:java.util.logging.LoggingPermission','control', '' ); 
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'setFactory', '' ); 
execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','accessCl
assInPackage.sun.util.calendar','');
execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','getClassLoader','');
execute dbms_java.grant_permission('<user_name>','SYS:java.net.SocketPermission','*','connect,resolve');
execute dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','*','read,write');
execute dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','setFactory','');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','shutdow
nHooks', '' );
call dbms_java.grant_permission('<user_name>','SYS:java.util.logging.LoggingPermission', 'control', '' );
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxySet','write');
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','getClassLoader','');
call dbms_java.grant_permission('<user_name>','SYS:java.net.SocketPermission','*','connect,resolve');
call dbms_java.grant_permission('<user_name>','SYS:java.util.PropertyPermission','*','read,write');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','setFactory','');
call dbms_java.grant_permission('<user_name>','SYS:java.lang.RuntimePermission','accessCl
assInPackage.sun.util.calendar','');  
call dbms_java.grant_permission( '<user_name>','SYS:oracle.aurora.security.JServerPermission', 'Verifier', '' );
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','' ) ;
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '<<machineName>>', 'resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '192.168.4.146', 'connect,resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'createClassLoader', '' );
call dbms_java.grant_permission( '<user_name>','SYS:java.lang.RuntimePermission', 'setFactory', '' );
call dbms_java.grant_permission( '<user_name>','SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write');
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', 'localhost', 'resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.net.SocketPermission', '192.168.3.229:80', 'connect,resolve' );
call dbms_java.grant_permission( '<user_name>','SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write');




Oracle DB Health Scripts 2 - I/O,cpu_time,user_time,wait_time,PGA_memory


SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait
Time",
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-10
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0
ORDER BY 2 DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                                     AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS
            from
              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (12/24);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select name,round(value/1024/1024) MB from v$pgastat where name in ('total PGA allocated','maximum 
PGA allocated')
union all
select 'alloc v$process',round(sum(pga_alloc_mem)/1024/1024) MB from v$process
union all
select 'max PGA v$process',round(sum(pga_max_mem)/1024/1024) MB from v$process
union all
select name,round(value/1024/1024) MB from v$sysstat where name like '%pga%';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

session and Process wise PGA memory usage :-

SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
                  nvl(lower(ssn.machine), ins.host_name) "SESSION",
             to_char(prc.spid, '999999999') "PID/THREAD",
             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",
             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"
    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
          v$instance ins,  v$statname stat1, v$statname stat2
 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session pga memory max'
 AND      se1.sid        = ssn.sid
 AND      se2.sid        = ssn.sid
 AND      ssn.paddr      = bgp.paddr (+)
 AND      ssn.paddr      = prc.addr  (+)
ORDER BY &sort_order DESC;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

session and Process wise UGA memory usage :-

SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
                  nvl(lower(ssn.machine), ins.host_name) "SESSION",
             to_char(prc.spid, '999999999') "PID/THREAD",
             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",
             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"
    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
          v$instance ins,  v$statname stat1, v$statname stat2
 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session uga memory'
 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session uga memory max'
 AND      se1.sid        = ssn.sid
 AND      se2.sid        = ssn.sid
 AND      ssn.paddr      = bgp.paddr (+)
 AND      ssn.paddr      = prc.addr  (+)
ORDER BY &sort_order DESC;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





memory leak


Recently i found a interesting situation where a LQA server has only 3 GB of RAM and  5 GB of swap . I assigned the 20GB of memory to the Oracle and re-start it  and without any problem it started smoothly . I tried to found out the memory leakage on this server but in vain , and the most interesting part is when all the users log on this server , this worked fine without any problem .Again i re-assigned the memory to it  according to the RAM and requirement . :)

Sunday, 6 January 2013

Find out invalid objects in Oracle Database and Generate scripts to compile


Find out invalid objects in Oracle and compile them :-

select owner,count(*),object_type from dba_objects where status='INVALID' group by owner,object_type;

select owner,object_name,object_type from dba_objects where status='INVALID' order by owner;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Generate Oracle scripts to compile  procedure , function , package , package body,trigger, view :-


select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME;
===========================================================


query about catalog and catproc :-

select substr(comp_id,1,12) comp_id, status, substr(version,1,10) version, substr(comp_name,1,40) comp_name from dba_registry order by 1;





get ddl of dblinks ,tablespace,user creation, role granted


Get ddl of database links in Oracle Database :-

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

==================================================================
Get ddl of tablespace creation in Oracle Database :- 

 select 'create tablespace ' || df.tablespace_name || chr(10)
 || ' datafile ''' || df.file_name || ''' size ' || df.bytes 
 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize ' 
 || maxbytes) 
 || chr(10) 
 || 'default storage ( initial ' || initial_extent 
 || decode (next_extent, null, null, ' next ' || next_extent )
 || ' minextents ' || min_extents
 || ' maxextents ' ||  decode(max_extents,'2147483645','unlimited',max_extents) 
 || ') ;'
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name 
/
=====================================================================
Get ddl of table and index :-

select DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','OWNER') from dual; 

select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

==================================================================
Get ddl of User creation in Oracle :-

select 'create user ' || U.username || ' identified ' || 
DECODE(password, 
      NULL, 'EXTERNALLY', 
      ' by values ' || '''' || password || ''''
      ) 
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace || 
decode (account_status,'LOCKED', ' account lock',
      'EXPIRED', ' password expire',
      'EXPIRED \& LOCKED', ' account lock password expire',
      null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;


or use :-

SELECT dbms_metadata.get_ddl('USER','<username>') from dual;
==============================================================
Get ddl of dba_directories: -

select dbms_metadata.get_ddl('DIRECTORY',directory_name) from  dba_directories;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<username>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<username>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<username>') from dual;

SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE') from dual;