Oracle Exadata Dba Solution

Monday, November 28, 2016

Oracle Sql Session status check query

Sql queries to check ACTIVE / INACTIVE Sessions

Count of sessions:

select count(s.status) TOTAL_SESSIONS

from gv$session s;

Count of Inactive sessions:

select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN Last 1HOUR:

select count(s.status) "INACTIVE SESSIONS > 1HOUR "

from gv$session s, v$process p

where

p.addr=s.paddr and

s.last_call_et > 3600 and

s.status='INACTIVE';

COUNT OF ACTIVE SESSIONS:

select count(s.status) ACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='ACTIVE';

TOTAL SESSIONS COUNT ORDERED BY PROGRAM:

col program for a30

select s.program,count(s.program) Total_Sessions

from gv$session s, v$process p

where p.addr=s.paddr

group by s.program;

TOTAL COUNT OF SESSIONS ORDERED BY MODULE:

col module for a30

prompt TOTAL SESSIONS

select s.module,count(s.sid) Total_Sessions

from gv$session s, v$process p

where p.addr=s.paddr

group by s.module;

TOTAL COUNT OF SESSIONS ORDERED BY ACTION:

col action for a30

prompt TOTAL SESSIONS

select s.action,count(s.sid) Total_Sessions

from gv$session s, v$process p

where p.addr=s.paddr

group by s.action;

INACTIVE SESSIONS:

prompt INACTIVE SESSIONS

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE:

prompt INACTIVE SESSIONS

select count(s.status) INACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE PROGRAMS:

col module for a40

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, v$process p

where p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE PROGRAMS with disk reads:

prompt INACTIVE SESSIONS

select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

GROUP BY S.PROGRAM;

INACTIVE SESSIONS COUNT WITH PROGRAM:

col program for a30

prompt TOTAL INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s,v$process p

where p.addr=s.paddr AND

s.status='INACTIVE'

group by s.program

order by 2 desc;

TOTAL INACTIVE SESSIONS MORE THAN 1HOUR:

col program for a30

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Inactive_Sessions_from_1Hour

from gv$session s,v$process p

where p.addr=s.paddr AND

s.status='INACTIVE'

and s.last_call_et > (3600)

group by s.program

order by 2 desc;

TOTAL INACTIVE SESSIONS GROUP BY MODULE:

col program for a60

COL MODULE FOR A30

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.module,count(s.module) Total_Inactive_Sessions

from gv$session s,v$process p

where p.addr=s.paddr AND

s.status='INACTIVE'

group by s.module;

INACTIVE SESSION DETAILS MORE THAN 1 HOUR:

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et_Hrs for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.last_call_et > (3600)

order by last_call_et;

INACTIVE PROGRAM :

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

And s.program='&PROGRAM_NAME'

order by last_call_et;

INACTIVE MODULES

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr

And s.module like '%order_cleanup_hazmat_v3.sql'

order by last_call_et;

INACTIVE JDBC SESSIONS:

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,

s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR:

SELECT COUNT(P.SPID)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

TOTAL FORM SESSIONS:

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%');

FORMS SESSIONS DETAILS:

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600

order by spid;

col machine for a15

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

S.ACTION,s.process Client_Process,s.machine

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600;

order by 4;

INACTIVE FORMS SESSIONS DETAILS:

col program for a15

col last_call_et for 999.99

select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

order by last_call_et desc;

UNIQUE SPID:

select unique(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

COUNT FORMS:

select COUNT(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

ZERO HASH VALUE:

select COUNT(p.spid)

from gv$session s,gv$process p

where

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

AND S.SQL_HASH_VALUE=0;

INACTIVE FORM BY NAME:

select count(s.sid) from v$session S

where s.action like ('%&ACTION%')

AND S.STATUS='INACTIVE';

GROUP BY ACTION:

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%')

group by s.action;

FROM A SPECIFIC USERNAME:

SET LINSIZE 152

col spid for a10

col process_spid for a10

col user_name for a20

col form_name for a20

select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,

d.user_form_name "FORM_NAME"

from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,

apps.fnd_form_tl d

where

a.login_id=b.login_id

and c.user_name like 'JROMO'

and a.user_id=c.user_id

and trunc(b.start_time) >trunc(sysdate -11)

and trunc(b.end_time) is null

and b.form_id=d.form_id

and d.language='US';

INACTIVE FORM:

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like ('FRM%')

and s.last_call_et > (3600*3)

order by last_call_et;

INACTIVE FORM SESSIONS :

col cli_proc for a9

COL AUDSID FOR A6

COL PID FOR A6

COL SID FOR A5

COL FORM_NAME FOR A25

COL USER_NAME FOR A15

col last_call_et for 9999.99

SELECT

-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */

( SELECT SUBSTR ( fu.user_name, 1, 20 )

FROM apps.fnd_user fu

WHERE fu.user_id = fl.user_id

) user_name,vs.status,

TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,

TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,

vs.last_call_et/3600 last_call_et,

SUBSTR ( fl.process_spid, 1, 6 ) spid,

SUBSTR ( vs.process, 1, 8 ) cli_proc,

SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,

SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,

SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,

SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,

SUBSTR ( vs.module || ' - ' ||

( SELECT SUBSTR ( ft.user_form_name, 1, 40 )

FROM apps.fnd_form_tl ft

WHERE ft.application_id = rf.form_appl_id

AND ft.form_id = rf.form_id

AND ft.language = USERENV('LANG')

), 1, 40 ) form_name

FROM apps.fnd_logins fl,

gv$process vp,

apps.fnd_login_resp_forms rf,

gv$session vs

WHERE fl.start_time > sysdate - 7 /* login within last 7 days */

AND fl.login_type = 'FORM'

AND fl.process_spid = vp.spid

AND fl.pid = vp.pid

AND fl.login_id = rf.login_id

AND rf.end_time IS NULL

AND rf.audsid = vs.audsid

and vs.status='INACTIVE'

ORDER BY

vs.process,

fl.process_spid;

ACTIVE:

prompt ACTIVE SESSIONS

select count(s.status) ACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='ACTIVE';

MODULE:

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr

and s.MODULE like ('&MODULE_NAME_1HR%')

and s.last_call_et > ('&TIME_HRS' * 3600)

order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

p.addr=s.paddr

and s.MODULE like ('%TOAD%')

Order by last_call_et;

TOAD SESSIONS:

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$process p

where

p.addr=s.paddr

and s.MODULE like ('%TOAD%')

Order by last_call_et;

CLIENT MACHINE SESSIONS COUNT:

select count(s.process) TOTAL from v$session S

where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

and s.status='INACTIVE';

hash value=0:

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0

AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

s.machine like ('%&CLIENT_MACHINE%') AND

p.addr=s.paddr and

s.status='INACTIVE';

GROUP BY program

col program for a60

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr AND

s.machine like ('%&CLIENT_MACHINE%') AND

s.status='INACTIVE'

group by s.program;

Wednesday, November 23, 2016

What is oracle Threshold

Threshold I meaning in English is exit door, door frame, doorsill . That means threshold is like a point where near to exit from where .

Like we are standing on threshhold for entering a new era .

oracle threshold like Tablspace threshold where you may set a threshold value when system reach
in the value em thrugh a alert that you reach a threshold level and now need to take action .
This threshold value and alert you can set and minotor from Enter prise manager email notification

1.Configuration Threshold Monitoring

a> Threshold Rules may define . (this rules vary for the type of resourses monitor also various event )

b> Define threshold rule sets from the rules you have already define.The rules set agreegate threshold rules for deployment to specific devices
c> Assign threshold rule sets to specific devices.
d> update the agent for putting the new rules sets into action

What Is Threshold Rules Sets

A threshold rule set is a collection of threshold rules. Threshold rules are defined separately and later combined under the umbrella of a threshold rule set.

Notification Options

Notification may take place by email and/or SNMP trap.

    Configure SNMP server information using the Administration task:

    Administration --> Server Configuration --> SNMP Administration

    Configure email server information using the Administration task:

    Administration --> Server Configuration --> Email Administration

    If you are notifying via email, you will enter the Configuration Change Console user (defined in the People screen) to receive the notification. The email accounts for individuals are created in:

    Administration --> People --> People

Friday, September 2, 2016

warning: RMAN-08591:Invalid archivelog deletion policy

RMAN-08591: WARNING: invalid archived log deletion policy

If getting error when taken backup of rman for archive log deletion policy dont upset your datagurd broker binding parameter value may be set optional . For remove this warning from backup follow below steps:

[oracle@drawdbadm01 ~]$ dgmgrl sys/***
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - ORCL_PRIMARY

Protection Mode: MaxPerformance
Databases:
    ORCL    - Primary database
    ORCLSTBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show configuration

check verbosely only stand by database [here my stand by db name orclstby]

Check the binding value for the standby database on DR side.

DGMGRL> show database verbose 'ORCLSTBY';

Database - AWCTGFO

Role:            PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag:   50 minutes 52 seconds (computed 1 second ago)
Apply Lag:       50 minutes 53 seconds (computed 0 seconds ago)
Apply Rate:      471.00 KByte/s
Real Time Query: ON
Instance(s):
    orclstby11 (apply instance)
    orclstby2

Properties:
    DGConnectIdentifier             = 'orclstby_dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '10'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA_DCAW, +DATA_DRAW, +RECO_DCAW, +RECO                                                                                        _DRAW'
    LogFileNameConvert              = '+DATA_DCAW, +DATA_DRAW, +RECO_DCAW, +RECO                                                                                        _DRAW'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold = '30'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL>

Now just change the bindinf value from optional to mandatory with datguard broker.

DGMGRL> edit database 'ORCLSTBY' set property Binding='mandatory';
Property "binding" updated
DGMGRL> exit

dgmgrl sys/********

DGMGRL> show configuration

check verbosely only stand by database [here my stand by db name orclstby]

DGMGRL> show database verbose 'ORCLSTBY';

Database - ORCSTBY

Role:            PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag:   50 minutes 52 seconds (computed 1 second ago)
Apply Lag:       50 minutes 53 seconds (computed 0 seconds ago)
Apply Rate:      471.00 KByte/s
Real Time Query: ON
Instance(s):
    orclstby11 (apply instance)
    orclstby2

Properties:
    DGConnectIdentifier             = 'orclstby_dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '10'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA_DCAW, +DATA_DRAW, +RECO_DCAW, +RECO                                                                                        _DRAW'
    LogFileNameConvert              = '+DATA_DCAW, +DATA_DRAW, +RECO_DCAW, +RECO                                                                                        _DRAW'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold = '30'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL>

Now just change the binding value from optional to mandatory with dataguard broker.

DGMGRL> edit database 'ORCLSTBY' set property Binding='mandatory';
Property "binding" updated

Now check the binbind parameter value changed mandatory .

DGMGRL> show database verbose 'AWCTGFO';

Database - AWCTGFO

Role:            PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag:   1 hour(s) 30 minutes (computed 1 second ago)
Apply Lag:       1 hour(s) 30 minutes 1 second (computed 1 second ago)
Apply Rate:      466.00 KByte/s
Real Time Query: ON
Instance(s):
    awctgfo1 (apply instance)
    awctgfo2

Properties:
    DGConnectIdentifier             = 'awctgfo_dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'mandatory'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '10'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA_DCAW, +DATA_DRAW, +RECO_DCAW, +RECO_DRAW'
    LogFileNameConvert              = '+DATA_DCAW, +DATA_DRAW, +RECO_DCAW, +RECO_DRAW'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold = '30'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS
second option to reset the value for binding

Sqlplus / as sysdba

SQL>alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST mandatory';

System altered.

RMAN> show ARCHIVELOG DELETION POLICY ;

Reset the value using configure with clear option.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY clear;

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

old RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

Thursday, September 1, 2016

Static listener vs Dynamic listener differences

Difference between Static listener and Dynamic listener registrations

Database can be registered in two ways with a listener

1. Static Registration :- A static registration of database to a listener is used when we want to remotely start our database. Static registration is like hardcoding a instance details in listener.ora file. In a static registration an instance is registered with the listener whether its up or not. When a client request comes listener opens a dedicated connection , and server later find out that instance is not up then it gives error message as "Oracle not available".

2. Dynamic Registration :- In Dynamic registration , registration is performed by PMON process. Once a Database instance starts, its PMON process registers instance details with associated listener. Dynamic registration does not require any manual configuration in the listener.ora file where as Static configuration does.From Oracle 8i dynamic configuration introduced.

PFB listener.ora file having static configuration :-

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = DCPROD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
    (SID_DESC =
      (SID_NAME = DCPROD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.26.241)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)

ADR_BASE_LISTENER = /oracle_home/oracle

PFB listener.ora file having dynamic configuration :-

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.26.242)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /oracle_home/oracle

As we can see dynamic configuration is more simple then static configuration.

PFB listener status details when using Dynamic regitration :-

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-SEP-2016 23:19:29

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.100.26.241)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                01-SEP-2016 23:07:46
Uptime                    0 days 0 hr. 11 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.100.26.241)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "DCPROD" has 2 instance(s).
Instance "DCPROD", status UNKNOWN, has 2 handler(s) for this service...
Instance "dcprod", status READY, has 1 handler(s) for this service...
Service "dcprodXDB" has 1 instance(s).
Instance "dcprod", status READY, has 1 handler(s) for this service...
The command completed successfully

Above status as "READY" resembles that listener has checked instance details and found its status as up or running to handle client request.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amit)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production

Start Date 01-DEC-2013 16:42:04

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP ON

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /oracle_home/oracle/diag/tnslsnr/new/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.100.26.242)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

Above status resembles that either Instance is not up, or its not been registered with listener.

PFB commands to register database instance with listerner if its not automatically registering with listener :-

[oracle@dbrac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 23:22:18 2016

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

SQL> alter system register;

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

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

[oracle@dbrac1 lsnrctl status

PFB listener status details when using static regitration also

Above status as "UNKNOWN" means that listener has not checked instance status details that whether its up or not, it has just registered the details of instance. So listener is not sure that whether instance can handle client request or not.

Monday, November 28, 2016

Oracle Sql Session status check query

Sql queries to check ACTIVE / INACTIVE Sessions

Wednesday, November 23, 2016

What is oracle Threshold

What is oracle Threshold

Friday, September 2, 2016

warning: RMAN-08591:Invalid archivelog deletion policy

RMAN-08591: WARNING: invalid archived log deletion policy

If getting error when taken backup of rman for archive log deletion policy dont upset your datagurd broker binding parameter value may be set optional . For remove this warning from backup follow below steps:

Thursday, September 1, 2016

Static listener vs Dynamic listener differences

Difference between Static listener and Dynamic listener registrations

Blog Archive