standby database ,rman incremental standby recovery

The  situation of  standby database lagging behind to primary database due to network outage can be tacked, without rebuild in Oracle 10g environment by following the below steps. The database version must be greater than 10.1.0.4 and and in a situation where scping of thousands of archives logs is not feasible
Steps to bring the standby database in sync with Primary Database is listed below.

1. Find the current SCN of standby database.

select current_scn from v$database;
CURRENT_SCN
———–
4793543

2. On the primary database – create the needed incremental backup from the above SCN

rman target /
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 4793543 DATABASE FORMAT ‘\opt\bkup_%U’;

3. create a new standby controlfile from production

sqlplus> alter database create standby controlfile ‘stdby’;

4.cancel managed recovery on standby
sqlplus> recover managed standby database cancel;

5. Move your incremental backup from (2) to the standby server (empty folder) and catalog it
rman target /
rman> catalog start with ‘\opt\backup\’;

6. recover your standby from the incremental backup
rman> recover database noredo;

7. shutdown the standby and replace the controlfile with the one you backup in (3)

8.startup the standby and put it back into managed recovery mode
sqlplus> startup mount
sqlplus> alter database recover  managed standby database disconnect;

Top 5 Session consuming buffers in oracle database

prompt
prompt **Top 5 by Buffer Gets:
prompt

set linesize 2000
set pagesize 100
set TIMI ON
select d.sql_text sql,'''', x.sid,'''',x.serial#, '''',x.username,'''',x.machine,'''', x.module,'''', x.program,'''', x.hash_value  from (SELECT * FROM
(SELECT s.sid,s.serial#, s.username,s.machine, a.hash_value, a.address, s.module, s.program, a.buffer_gets  FROM
V$SQLAREA a, v$session s
WHERE a.buffer_gets > 10000 and a.hash_value = s.sql_hash_value and a.address=s.sql_address and s.sid !=(select distinct sid from v$mystat)
ORDER BY buffer_gets DESC)
WHERE rownum <= 5) x, v$sqltext_with_newlines d where x.hash_value=d.hash_value and x.address=d.address order by x.buffer_gets desc, x.hash_value, d.piece;

Delete Old archives which are more than 3 days old in oracle database

Below shell scrip Can be used for deleting old archives from oracle database which are > 3 days old
Description of each value
/bin/find: Location of find executable
/opt/DB_ARCH/  : Location to be searched




Remove  Archived Logs after 3 days
--------------------------------------------------------
00 07 * * * /bin/ksh -c '/bin/find /opt/DB_ARCH/ -type f -mtime +3 -name "arch*.arc" -print -exec rm {} \;' 2>&1  >>/opt/DB_ARCH/archives.deleted.log

This script can also be customized to delete the traces and other files based on your requirement

Recompile Invalid objects in oracle database

Below Scripts can be used for making the database objects valid

set feedback off
      set verify off
      set echo off
      set pagesize 0
      set heading off
      spool compile_invalid_objects

select  'alter ' ||
             decode(object_type, 'PACKAGE BODY', 'package', object_type) ||
             ' ' ||owner ||'.'||
             object_name||
             ' compile' ||
             decode(object_type, 'PACKAGE BODY', ' body;', ';')
      from   dba_objects
      where  status = 'INVALID';

      spool off
      set feedback on
      set verify on
      set heading on
      set pagesize 40
      @compile_invalid_objects

How to resize a datafile and free up the space at OS level

execute below sql query to find out how much space can be reclaimed at dataffile level as below
SQL> select file_name,
  2  ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
  3    4  ceil( blocks*&&blksize/1024/1024) -
  5  ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
  6  from dba_data_files a,
  7  ( select file_id, max(block_id+blocks-1) hwm
  8  from dba_extents
  9  group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11  /

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/Ora10g/prod/system01.dbf                   421      544      123
/Ora10gdb10/oradata/prod/collectord01.dbf             1,223    1,374      151
/sv101/db14/oradata/prod/prdsselx00p_06.dbf           19,991   20,000        9
/Ora10gdb01/oradata/prod/disceuld_01.dbf              5,502    5,502        0
/Ora10gdb03/oradata/prod/prodprds2d01.dbf               1,585    1,585        0
/Ora10gdb03/oradata/prod/prodprds2x01.dbf               1,350    1,350        0
/Ora10gdb07/oradata/prod/prdsotlhx00p_01.dbf          19,991   19,991        0
/Ora10gdb08/oradata/prod/prdssrd00p_01.dbf            24,991   25,000        9
/Ora10gdb09/oradata/prod/prdssrx00p_01.dbf            17,251   17,401      150
/Ora10gdb08/oradata/prod/prdssrd00p_02.dbf            24,991   25,000        9
/Ora10gdb04/oradata/prod/tools01.dbf                     20       20        0
/Ora10gdb01/oradata/prod/users_01.dbf                     4       10        6
/Ora10gdb05/oradata/prod/ctsreportsd02.dbf           11,511   11,550       39
/Ora10gdb01/oradata/prod/prdsmatrix_04.dbf            20,000   20,000        0
/Ora10gdb01/oradata/prod/prdsmatrix_05.dbf            20,000   20,000        0
/Ora10gdb01/oradata/prod/datamartd_05.dbf            19,997   19,997        0
/Ora10gdb01/oradata/prod/datamartd_06.dbf            19,997   20,000        3
/Ora10gdb01/oradata/prod/datamartd_07.dbf            19,997   20,000        3
/Ora10gdb05/oradata/prod/prdssachd00p_08.dbf           9,421   19,991   10,570

below query will provide the statement that can executed to shrink the datfiles and reclaim space at OS level
SQL> SQL> SQL> select 'alter database datafile ''' || file_name || ''' resize ' ||
  2  ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
  3  from dba_data_files a,
  4  ( select file_id, max(block_id+blocks-1) hwm
  5  from dba_extents
  6  group by file_id ) b
where a.file_id = b.file_id(+)
  7    8  and ceil( blocks*&&blksize/1024/1024) -
  9  ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
 10  /

alter database datafile '/Ora10gdb10/oradata/prod/collectord01.dbf' resize
1223 m;

Why the session is taking more time than normal in oracle database ?

Step 1: Check the Alert log for errors
Step 2: Check with the end user on how long the session used to take to complete.
Step 3: Check with user whether any increase in the volume of data being
             Processed.
Step 4:  Check the wait events for this sessions by using the query given below
              select p1, p2, p3, event
from   v$session_wait
where sid=&session_id;
Also check the following:
select count(*), event
from    v$session_wait
group   by event;
Step 5:  Identify the tables begin accessed by that session using below query
select owner, object, type
from   v$access
where sid=&session_id
and     owner not in (‘SYS’,’SYSTEM’);
.
Step 6:  Check whether Statistics have been generated for these tables identified
              using the following query:
              select  last_analyzed, num_rows
              from   dba_tables
              where  owner=’&table_owner’
              and      table_name=’&object_name’;
              Note: If the table is partitioned, check in dba_tab_partitions also and
                         for subpartitions check in dba_tab_subpartitions.
Step 7: Check whether Statistics have been generated for the indexes of the tables
             Identified Using the following query:
             select  last_analyzed, num_rows
             from   dba_indexes
             where  table_owner=’&table_owner’
             and      table_name=’&table_name’;
             Note: If the table is partitioned, check in dba_ind_partitions and for
                       subpartitions check in dba_ind_subpartitions.
Step 8:  If Statistics is not up-to-date, Generate stats using dbms_stats package.
Step 9:  Follow the approach given in below for sql tuning.
Avoid Using the Following:
a. Boolean Operators, Is null & Is not Null.
b. not in, != Operators
c. like ‘%patterns’, not exists
          
             Do’s:
a. Enable aliases to prefix all columns.
b. Use sql joins instead of sub-queries
c. Make the tables with the least number of rows as the driving table by keeping them first in the FROM clause.
d. Use concatenated indexes wherever appropriate.
e. Pick up the Best Join method.
f. Nested loops joins are best for indexed joins of subsets.
g. Hash joins are usually the best choice for "big" joins
h. Pick the best "driving" table
i. Use bind variables. Bind variables are key to application scalability.
j. Use Oracle hints wherever appropriate
k. Compare performance between alternative syntax for your SQL statement

                  Use Explain Plan to Identify the Access path being used by the query.
                  Syntax is explain plan for actual_sql_statement;
                  You can see the output of the explain plan by running the following sql:
                   $ORACLE_HOME/rdbms/admin/utlxplp.sql
                  Alternatively, You can also trace the session by using the
                  following Command:
                  alter session set events '10046 trace name context forever,level 12';
                  Run the sql Query.  This will generate the trace file in udump directory.
                  Use tkprof utility to get the readable output of this trace file.  Use the
                  following Syntax:
                  tkprof trace_file_name trace_file_name.out sys=no explain=userid/pwd
                  This tkprof output file trace_file_name.out will have the access path
                  Used by the queries and as well the various timed statistics like
                  cpu time, elapsed time etc.

How to identify the wait events for a Session in oracle database

select p1, p2, p3, event

from   v$session_wait

where sid=&session_id;

Also check the following:

select count(*), event

from    v$session_wait

group   by event;

Max no of processes exceeded (ORA-00020) error in oracle database

Fix:

Step  1 : Connect to the database as sqlplus “/ as sysdba”

Step  2:  Identify the Inactive process based on some threshold (may be Session

              Inactive for more than 5 hours  (select  sid

from   v$session

where  paddr in (select addr

                           from   v$process

                           where  background is null)

and     status=’INACTIVE’

and     last_call_et/5*60/60>1; 

) using the query given in Point No 4

              and then kill them. (Check for the guidelines documents for

              killing the session with  your Senior dba’s/Manager/Client).

Long Term Solution:

Step 1: Write a script to Identify and report the Inactive Sessions

             Running for Long Hours and Schedule this in Cron. Analyze this report

             and take Corrective Action.

Step 2:  Check the following:

             select  SESSIONS_HIGHWATER, SESSIONS_MAX

             from    v$license;

             SESSION_HIGHWATER = Highest number of concurrent user sessions  

                                                            since the instance started.

          

             SESSIONS_MAX              = Maximum number of concurrent user

                                                            sessions allowed for the instance

Step 3: Check the Process parameter value set in init.ora file.

Step 4: If the Process parameter value is less than session_highwater value, 

            Take Necessary approvals and Get the downtime to Shutdown the  

             instance.

Step 5: Increase the value of the Process init.ora parameter upto the value of 

            session_highwater value.

How to identify the free space in undo tablespaces in oracle database

select sum(bytes)/1024/1024/1024, status, tablespace_name

from  dba_undo_extents

      group by status, tablespace_name;

      Definition of Transaction Status:

a.       ACTIVE means that this undo segment contains active transactions

b.      EXPIRED means that this segment is not required at all (as per undo_retention).

c.       UNEXPIRED means that this segment does not contain any active transactions but it contains transactions which are still required for Flashback option (as per Undo_retention).

troubleshooting temp space issues in oracle database

Monitoring Temporary Space Usage




SQL> SELECT   A.tablespace_name tablespace, D.mb_total,
  2           SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  3           D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  4  FROM     v$sort_segment A,
  5           (
  6           SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
  7           FROM     v$tablespace B, v$tempfile C
  8           WHERE    B.ts#= C.ts#
  9           GROUP BY B.name, C.block_size
 10           ) D
 11  WHERE    A.tablespace_name = D.name
 12  GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TEMP                                  4000          0       4000

SQL>


SQL> set time on timi on
09:07:31 SQL> SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
09:07:59   2           S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
09:07:59   3           COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
09:07:59   4  09:07:59   5  WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
09:07:59   6  09:07:59   7  AND      T.tablespace = TBS.tablespace_name
09:07:59   8  GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
09:07:59   9           S.program, TBS.block_size, T.tablespace
09:07:59  10  ORDER BY sid_serial;

no rows selected

Elapsed: 00:00:00.04
09:07:59 SQL>
09:08:01 SQL> SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
09:08:17   2           T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
09:08:17   3  09:08:17   4  FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
09:08:17   5  WHERE    T.session_addr = S.saddr
09:08:17   6  AND      T.sqladdr = Q.address (+)
09:08:17   7  AND      T.tablespace = TBS.tablespace_name
09:08:17   8  ORDER BY S.sid;

no rows selected

Elapsed: 00:00:00.02
09:08:17 SQL>

How to identify the rollback/Undo segments used by a session in oracle database

select *

from   v$rollname

where usn = (select xidusn

                      from   v$transaction

                      where addr in (select taddr

                                              from   v$session

                                              where sid=&session_id));

How to identify the sql used by a session in oracle database

select  sql.sql_text

      from   v$session ses, v$sqltext sql

      where sql.address=ses.sql_address

      and     sql.hash_value=ses.sql_hash_value

      and     sid=&session_id

      order   by piece;

4. How to identify the sessions which are Inactive for more than 1 hour in oracle database

select  sid

from   v$session

where  paddr in (select addr

                           from   v$process

                           where  background is null)

and     status=’INACTIVE’

and     last_call_et/60/60>1;  

3. How to Identify the Server Process Id using the oracle session id (sid) in oracle database

select spid

from  v$process

where background is null

and     addr in (select paddr

                        from   v$session

                        where  sid=&session_id);

         

How to identify the sid using Server Process id in oracle database

select  sid

from   v$session

where paddr in (select addr from v$process

                          where background is null

                          and    spid=&Server_process_id);

How to identify the sid using Client Process id in oracle database

select sid

from  v$session

where process=’&client_process_id’;

Monitoring Temporary Space in oracle database

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;