Session Monitoring

Blocking Sessions:

SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l2.id2 = l2.id2 ;

Sessions count:

select resource_name, current_utilization, max_utilization,LIMIT_VALUE,inst_id from gv$resource_limit where resource_name in ('processes','sessions');


Session level Wait events:

col program for a19
col event for a50
set lines 900 pages 900
col machine for a40
select inst_id,sid,program,sql_id,event,machine,state,logon_time,SECONDS_IN_WAIT,last_call_et/60 mins_running from gv$session
where username is not  null and program not like 'oraagent.bin%' and program not like 'emagent%' and program not like 'sqlplus%'
 and program not like 'oracle@%' and program<>'OMS' and event not like 'SQL*Net message%';

Session and SQL Running:

col sid form 9999
col curr form a120 head "     Current SQL"
bre on sid skip 2
set long 10000
select a.sid sid,b.sql_text curr
from gv$session a, gv$sql b
where a.sql_address=b.address
and a.sid=&1
order by 1;

ORA-16139 Physical standby with a switchover status of “NOT ALLOWED”

ORA-16139 Physical standby with a switchover status of “NOT ALLOWED”

The status on the standby (being switched to a primary) was as follows:

SQL> SELECT database_role, switchover_status FROM v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
PHYSICAL STANDBY NOT ALLOWED

The alert log indicated that not all logs had been applied to this standby before it was converted to a primary:


Switchover: Media recovery required - standby not in limbo
ORA-16139 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN...
At this point we brought up the database as a standby with the below commands:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> alter database recover managed standby database finish force;

This caused the last archive logs from the primary to be applied and the following to be displayed in the alert log:

Attempt to do a Terminal Recovery
Media Recovery Start: Managed Standby Recovery

Attempt to set limbo arscn 0:115809474 irscn 0:115809474
Completed: alter database recover managed standby database finish force
After the above, we issued:

SQL> alter database commit to switchover to primary with session shutdown;

And the alert log indicated that it was successful:

SQL>  alter database commit to switchover to primary with session shutdown

SQL> ALTER DATABASE SWITCHOVER TO PRIMARY

Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with session shutdown
At this point the database was in the below mode and switchover status:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
MOUNTED              PRIMARY          NOT ALLOWED
We then enabled the log_archive_dest_state_2 as this was now the primary and issued the below commands:

SQL> shutdown immediate;

SQL>  startup;
The database was now in the below mode and switchover status:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
READ WRITE           PRIMARY          RESOLVABLE GAP
After a couple of minutes, the status changed to:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
READ WRITE           PRIMARY          SESSIONS ACTIVE
The standby status was as follows:

SQL> SELECT open_mode, database_role, switchover_status FROM v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
MOUNTED              PHYSICAL STANDBY NOT ALLOWED

This is an expected status as per “Physical Standby Switchover_status Showing Not Allowed. (Doc ID 1392763.1)”. After the command to switch roles is issued on the primary, the primary will generate a special marker called EOR (end-of-redo) that is placed in the header of online redo log sequence. So this online redo log sequence will be archived locally and sent to all standby databases. Only upon receiving and applying EOR (end-of-redo), v$database.switchover_status will change from “not allowed” to “to primary” or “sessions active”.

TEMP TS USAGE SESSIONS

This summary is not available. Please click here to view the post.

ORA-19566: exceeded limit of 0 corrupt blocks

During full backup operation We noticed that RMAN log mention below error message at backup log file.

RMAN-03009: failure of backup command on ch01 channel at 01/24/2018 20:05:49
ORA-19566: exceeded limit of 0 corrupt blocks for file xxxxxxxxxxxxxxxxxxxxxx

Get the Datafile id and block id using below.

select * from v$database_block_corruption;

select 'Validate datafile '|| file# || ' block '||block# ||';' FROM v$database_block_corrupt;

Validate using rman:

Validate datafile <file no> block <Block no> ;

RMAN> VALIDATE DATAFILE 1;
RMAN> VALIDATE DATAFILE '/u01/app/oracle/oradata/xxxx/xxxxxxxxxx.dbf';

RMAN> VALIDATE CHECK LOGICAL DATAFILE 1;
RMAN> VALIDATE CHECK LOGICAL DATAFILE '/u01/app/oracle/oradata/xxxxxx/xxxxxxx.dbf';

RMAN> VALIDATE TABLESPACE users;
RMAN> VALIDATE CHECK LOGICAL TABLESPACE users;

RMAN> VALIDATE DATABASE;

RMAN> VALIDATE CHECK LOGICAL DATABASE;

See what type of object is corrupted.

select * from v$database_block_corruption;

select segment_name,owner,segment_type from dba_extents
     where file_id=xx
     and xxxxxxx between block_id and block_id + blocks -1; 

SEGMENT_NAME              OWNER      SEGMENT_TYPE
------------------------- ---------- ------------------
xxxxxxxx       SYS        INDEX PARTITION

Either you can ignore the corruption based on the object type or we can perform recovery.
Ignoring the corruption:

run
{
ALLOCATE CHANNEL ch1 TYPE DISK;
set maxcorrupt for datafile xx to xx;
RELEASE CHANNEL ch1;
}

Performing recovery :

Rman> run { 
allocate channel c1 device type disk ;
allocate channel c2 device type disk ; 
blockrecover corruption list ; 
}

Or Alternate command is

Rman>recover corruption list ;

While recover corruption list in process, dynamic view v$database_block_corruption will be updated. 
But We see that after rman complete process this view still show us there are corruption block avaliable.
So validate using rman

rman target / nocatalog
RMAN> Validate datafile xx block xxxxxxxx;

Now check corruption list

select * from v$database_block_corruption;

DR Dril

GRACEFUL SWITCH OVER:
++++++++++++++++++++++++

verify on Standby and Primary:
==============================

select name,database_role,switchover_status from v$database;
select process from v$managed_standby where process like 'MRP%';
select thread#, max(sequence#) from v$archived_log alog, 
 v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;
 select sequence#,thread#,process,status from gv$managed_standby

select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select sequence#,applied,completion_time from v$archived_log;


PRIMARY: (ORIGINAL PRIMARY)
======================


SWITCHOVER_STATUS (TO STANDBY)

1. Capture current job state on the primary:
select name,database_role,switchover_status from v$database;

SQL> select * from dba_jobs_running; [depending on what the running job is, be ready to terminate]
SQL> select owner,JOB_NAME,STATE,ENABLED,LAST_START_DATE,LAST_RUN_DURATION from dba_scheduler_jobs where enabled='TRUE';
SQL> select * from dba_scheduler_running_jobs;
SQL> show parameter job_queue_processes

Note: Cron job candidates to be disabled among others! 

2. Block further job submissions:

SQL> alter system set job_queue_processes=0 scope=both sid=’*’;
SQL> execute dbms_scheduler.disable(job_name);

3. Disable any cron jobs that may interfere if any
. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database, for example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

4. Shutdown all but one database instance.

 5. Switch the current primary over to the standby role.
alter database commit to switchover to physical standby with session shutdown;

6. Mount the database & put in Managed Mode.
shutdown immediate;

startup nomount;

alter database mount standby database;

recover managed standby database using current logifile disconnect;

SELECT SWITCHOVER_STATUS,database_role FROM V$DATABASE

SWITCHOVER_STATUS (TO PRIMARY)

Standby:
======================


7. Initiate the switchover:

alter database commit to switchover to primary with session shutdown;

8. Next, open all of the database instances:

ALTER DATABASE OPEN;

9. Enable database job submissions:

ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH SID='*';

Ensure that the new primary and standby are syncing properly by reviewing the alert logs and doing some log switches


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

GRACEFUL SWITCH BACKU:
++++++++++++++++++++++++

SWITCHOVER_STATUS (TO STANDBY)

verify:
========


1. Capture current job state on the Standby:
select max(sequence#) from v$archived_log;

select max(sequence#) from v$log_history; ----Primary

select sequence#,applied,completion_time from v$archived_log; -----> Standby

select name,database_role,switchover_status from v$database;
select process from gv$managed_standby where process like 'MRP%';
select thread#, max(sequence#) from v$archived_log alog, 
 v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;
 select sequence#,thread#,process,status from gv$managed_standby


PRIMARY: (OLD STANDBY)
======================


2. Capture current job state on the primary:
select name,database_role,switchover_status from v$database;

SQL> select * from dba_jobs_running; [depending on what the running job is, be ready to terminate]
SQL> select owner,JOB_NAME,STATE,ENABLED,LAST_START_DATE,LAST_RUN_DURATION from dba_scheduler_jobs where enabled='TRUE';
SQL> select * from dba_scheduler_running_jobs;
SQL> show parameter job_queue_processes

Note: Cron job candidates to be disabled among others! 

2. Block further job submissions:

SQL> alter system set job_queue_processes=0 scope=both sid=’*’;
SQL> execute dbms_scheduler.disable(job_name);

3. Disable any cron jobs that may interfere if any
. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database, for example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

4. Shutdown all but one database instance.

5. Switch the current primary over to the standby role.

alter database commit to switchover to physical standby with session shutdown;

6. Mount the database & put in Managed Mode.
shutdown immediate;

startup nomount;

alter database mount standby database;

recover managed standby database using current logifile disconnect;

SELECT SWITCHOVER_STATUS,database_role FROM V$DATABASE

SWITCHOVER_STATUS (TO PRIMARY)
Standby;( OLD PRIMRY)
======================


7. Initiate the switchover:
select name,database_role,switchover_status from v$database;
alter database commit to switchover to primary with session shutdown;

8. Next, open all of the database instances:

ALTER DATABASE OPEN;

9. Enable database job submissions:

ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH SID='*';

Ensure that the new primary and standby are syncing properly by reviewing the alert logs and doing some log switches

Datagaurd Archive gap

How to check archivelog gap?

-- On STANDBY, open_mode should be set to MOUNTED at least:

SELECT log_mode, open_mode, database_role from v$database;

-- On STANDBY, MRP0 process should show "Applying_log":

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

select process, client_process, sequence#, status from V$managed_standby;

-- On STANDBY, determine if there are any archived log gaps
-- Query should return no rows:

Select * From v$archive_gap;

--  On STANDBY, compare last applied log file against last received archived log
--  Difference should not be greater than 2.

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;


set echo off
conn /  as sysdba
set echo on
-- On PRIMARY, list the sequence#s of the logs that were last archived.
-- These should match "Last seq received" from previous query:

select thread#, max(sequence#) from v$archived_log group by thread#;

-- On PRIMARY, find out which logs where not shipped to standby.
-- The query should return no rows:

Select thread#,sequence#
From v$archived_log a
 where dest_id=1
and a.sequence# >
     (select min(b.sequence#)-1 from v$archived_log b
        where b.dest_id=2
          and b.thread#=a.thread#)
Minus
Select c.thread#,c.sequence#
From v$archived_log c where c.dest_id=2;

DR SYNC STEPS;;;

1. Restore/Copy the archives to the location on standby

If archive logs are in ASM copy to file system and then to standby

RMAN> copy archivelog '+FLASH_DG/PRD/ARCHIVELOG/2012_09_02/thread_3_seq_93970.9317.792907937' to '/u01/rman01/prd/incbkp/thread_3_seq_93970.9317.792907937';

2.Register the archives with the database(alter database register logfile '/u01/rman01/prd/incbkp/thread_3_seq_93970.9317.792907937';)

3.alter database recover managed standby database disconnect;----will apply archives

4.select max(sequence#) from v$archived_log where applied='YES';(the count should be increasing)

Removing old trace files from ORACLE HOME based on time

Some times we will have huge number old trace files in ORACLE HOME which are not required. Below are the steps to clean up based on time

Find out trace file generated a month ago

find /u01/app/prod/oracle/admin/udump -mtime +30 -exec ls -ltrh {} \;

Then proceed for removing.

find /u01/app/prod/oracle/admin/udump/udump -mtime +30 -exec  rm  {} \;

-mtime can be changed based on the requirement.

We can use rm -rf , But should be very careful.

Below is for removing files on particular day using simple for loop.

Example, I wanted remove file on Jul 31.

cd  /u01/app/prod/oracle/admin/udump

for i in `ls -l  |grep " Jul 31" | awk '{ print $9}'`
do
rm $i
done