ORA-17630: Mismatch in the remote file protocol version client 1 server 3

I was trying to setup an Oracle Data Guard environment (physical standby) from an Oracle database running version 11.2.0.4 to target oracle db version is 12.2.0.1 using rman duplicate .

Source db : 11.2.0.4
Target db : 12.2.0.1


RMAN> run {
 allocate channel pri1 type disk;
 allocate channel pri2 type disk;
 allocate channel prmy4 type disk;
 allocate auxiliary channel stby type disk;
 duplicate target database for standby from active database
  spfile
  parameter_value_convert 'paras','vipul'
    set db_unique_name='vipul'
    set control_files='/u01/app/oracle/oradata/vipul/control01.ctl'
    set fal_client='vipul'
    set fal_server='paras'
 set log_archive_max_processes='5'
2> 3> 4> 5> 6>     set standby_file_management='AUTO'
    set log_archive_config='dg_config=(paras,vipul)'
 set db_file_name_convert='/u01/app/oracle/oradata/paras/','/u01/app/oracle/oradata/vipul/'
    set log_file_name_convert='/u01/app/oracle/oradata/paras/','/u01/app/oracle/oradata/vipul/'
    set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=vipul'
    set log_archive_dest_2='service=paras ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=paras';
  }
  7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20>
using target database control file instead of recovery catalog
allocated channel: pri1
channel pri1: SID=35 device type=DISK

allocated channel: pri2
channel pri2: SID=40 device type=DISK

allocated channel: prmy4
channel prmy4: SID=32 device type=DISK

allocated channel: stby
channel stby: SID=35 device type=DISK

Starting Duplicate Db at 03-DEC-19

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwparas' auxiliary format
 '/u01/app/oracle/product/12.2.0/db/dbs/orapwvipul'   targetfile
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileparas.ora' auxiliary format
 '/u01/app/oracle/product/12.2.0/db/dbs/spfilevipul.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0/db/dbs/spfilevipul.ora''";
}
executing Memory Script

Starting backup at 03-DEC-19
RMAN-03009: failure of backup command on pri1 channel at 12/03/2019 10:23:15
ORA-17629: Cannot connect to the remote database server
ORA-17630: Mismatch in the remote file protocol version client 1 server 3
continuing other job steps, job failed will not be re-run
released channel: pri1
released channel: pri2
released channel: prmy4
released channel: stby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/03/2019 10:23:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on pri2 channel at 12/03/2019 10:23:15
ORA-17629: Cannot connect to the remote database server
ORA-17630: Mismatch in the remote file protocol version client 1 server 3

RMAN>

RMAN>



Solutions :
===============

We need to apply below patch on source 11.2.0.4 db 

p29913194_112040_Linux-x86-64 - Required patch
p18633374_11204190716_Linux-x86-64  - Main patch
p6880880_112000_Linux-x86-64  - required version of opatch 


After apply patch 18633374 to the ORACLE_HOME that was running the SOURCE database, able to proceed with building out the standby successfully using RMAN DUPLICATE with ACTIVE DATABASE


but we need to sysnc then upgrade db on target for open, i will share steps to upgarde in next post .


Always read README before apply any patch . 

Oracle Tablespace Utilization Script

Tablespace Utilization Script for Tablespace Space Used % more than 85 %
==========================================================================

Check the tablespace Utilization.
=========================================

set pages 500 lines 250
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>85
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name;


Check the details of the datafiles for a particular TableSpace 
=====================================================================


set pages 500 lines 250
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name=UPPER('&TABLESPACENAME') order by 1,2,6;



Get DDL for tablespace 
==============================

set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE',upper('&TABLESPACE_NAME')) FROM DUAL;

Move datafile one location to different location online in oracle 12c Version 12.1.0.2 and later

Move datafile one location to different location  online in  oracle 12c Version 12.1.0.2 and later:
==========================================================================================================


Login as sysdba 



to get the file id and datafile loation info using below query 
=========================================================================
select FILE_ID ,FILE_NAME,TABLESPACE_NAME from dba_data_files ;



In OMF (Oracle Managed file name feature)
=====================================================

Error  while move datafile in OMF : 


SQL> ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data21/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data22/abcd_grlw8cvz_.dbf';
ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data1/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data1/abcd_grlw8cvz_.dbf'
*
ERROR at line 1:
ORA-01276: Cannot add file /oracle/oradata/data1/abcd_grlw8cvz_.dbf.  File has an Oracle Managed Files file name.


SQL>

Solution
==============

Set db_create_file_dest before moving the datafile and do not specify the datafile name

SQL> alter session set db_create_file_dest='<DIR_NEW>';
SQL> alter database move datafile 21;




In Normal 
==================

alter database move datafile '/u01/oradata/test/test.dbf' to '/u02/oradata/test/test.dbf';

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR OVER THE LAST WEEK:

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR OVER THE LAST WEEK:

Using below query you can find how many log switch in last week per hour basis. this will help to analyze how much database was busy in last week and what time db is so much busy .

You can set FRA based of log switch analysis .if db is running in archive log mode .




Query :
=============

SET PAGESIZE 500
SET LINESIZE 200
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <8;


Example :


SQL>   2  SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
  3    4  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
  5  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
  6  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
  7  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
  8  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
  9  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
 10   11  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
 12   13   14   15   16  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
 17   18   19  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
 20   21   22   23  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
 24   25  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
 26   27   28   29   30   31    ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <10; 32

DAY   00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
20/11    55   470    51     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0
19/11    54   417   878    83     0     0     4     5   272   516   175     0     2     0     0     0     0     0    12   159     0     1     4     0
18/11    94   380     0   399   577     0     1     3     0   101     0     0     2     0     0     0     0     1     9   150     0     1     1     0
17/11     0     0     0     1     0     0     5     9     0     0     0     0     2     0     0     0     0     0     3    14     0     1     0     0
16/11    16     0     0     0     0     0     0     0     1     0     0     0     2     0     0     0     0     0     0     3     0     0     0     0
15/11    60   505   707    84     0     0     9     8     0    97     0     1     1     0     0     0     0     1    21   167     0    35     0     0
14/11    58   422     0     0     0     0     5    16     0    96    50    32     2     0     1     0     0     0   807   166     0     1     0     0
13/11     0     0    10    35    35   419    19     7     0    98     0     0     2     0     0     0     0   356   953   163     0     1     0     0

8 rows selected.


SQL>

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

Using below query you can find how many redo log switched per hours in oracle database .it will show you how much database is busy .


Login as sys user and run below query .

Query:
============

select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
 from v$log_history 
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
 )
group by Hour
order by Hour;


Example :

SQL> select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
  2    3    4   from v$log_history
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
  5    6    7   )
group by Hour
  8    9  order by Hour;

HO LOG_SWITCHES
-- ------------
00           56
01          439
02          412
03          120
04          306
05          419
06            7
07            8
08          137
09          182
10          113
11           17
12            2
14            1
17          119
18          301
19          117
21            7
22            3

19 rows selected.

SQL>

How to run SQL Tuning Advisor in Oracle

Steps to Run SQL Tuning Advisor Against Slow Running Queries in Oracle

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

STEP 1: Create tuning task for the specific SQL_ID:

declare
task_nm varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
end;
/

NOTE: Replace sql_id in above statement


STEP 2: Check the status of newly created task:

SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

STEP 3: Execute the newly created task:

exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');
Note: Please replace the task name as mentioned in step 1

STEP 4: Check the status after executing the task:

SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

*Task should be completed, it will take some time to complete.

STEP 5: Execute the Below Query to get the Advisory Report:


SET LONG 99999;
SET PAGESIZE 1000
SET LONGCHUNKSIZE 65536
SET LINESIZE 200

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;


NOTE: Replace task name in above query

STEP 6: To Drop the Tuning Task:

execute dbms_sqltune.drop_tuning_task('&TASK_NAME');


To execute tuning advisory using AWR snap ID if sql_id is not present in cursor:


STEP 7: Find the snap ID using below query:

SQL>
SELECT SQL_ID, PLAN_HASH_VALUE, TIMESTAMP FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='&SQL_ID';

SQL>
select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='&SQL_ID' and plan_hash_value='&plan_hash_value' order by snap_id desc;



STEP 8: Create Tuning Task:

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 841,
end_snap => 891,
sql_id => '663zvxqjuxtqn',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '663zvxqjuxtqn_AWR_tuning_task',
description => 'Tuning task for statement 663zvxqjuxtqn in AWR');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

--NOTE: Replace the above highlighted values:



STEP 9: After creation of tuning task you can follow the above steps 

Monitor job progress :-

COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$ADVISOR_PROGRESS;

How to Identify SID Based on OS PID in Oracle

How to Identify SID Based on OS PID in Oracle

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

Query to check SID from PID (get from top command )

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

set lines 200 pages 500

col sid format 99999
col username format a30
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser
from gv$session s, gv$process p
where s.paddr= p.addr
and p.spid='&spid'

order by p.spid;

Purge AWR Snapshots in oracle

Purge AWR Snapshots

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

• Get the snapshot details  : 

SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

• purge snapshot by given range :

First Method:

exec dbms_workload_repository.drop_snapshot_range(881,991);



Second Method:

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id  => 881,
high_snap_id => 991);
END;
/  

Get the client IP address from a session in oracle

Get the client IP address from a session in oracle .  There are two way to get IP

•  SYS_CONTEXT('USERENV','IP_ADDRESS')
•  UTL_INADDR.GET_HOST_ADDRESS

Query1 : using :  SYS_CONTEXT('USERENV','IP_ADDRESS')
=============================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Query2: using  UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP
=========================================================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;

Extract Index DDL from database export dump

PAR file for index ddl extract from dump using traditional import utility
===================================================================

userid= '/ as sysdba '
statistics=none
buffer=9999999
log=/oracle/export/IND/dump/imp_index_full_10122019.log
constraints=n
grants=n
rows=n
FROMUSER=HR
indexfile=/oracle/export/IND/dump/index3.sql
Tables=(EMPLOYEES)
file=(/oracle/export/IND//exp_full_IND.dmp)


To execute the command in background 
===========================================

nohup imp parfile=file_name.par &

Enable Trace in oracle for SID

Get SID :
================

column SID for a20
SELECT sys_context('USERENV', 'SID') SID FROM DUAL;


Enable Trace for SID  in oracle 
==================================


SQL> select username,sid,serial# from v$session where sid=22;

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SYS                                    22       2895

SQL>




alter session set tracefile_identifier='anurag_sql_trace';

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>22, serial#=>2895, sql_trace=>TRUE)

---To disable

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

--- Get the tracefile name:

SELECT p.tracefile FROM   v$session s  JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = 22;

TRACEFILE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc

Manually Corrupting Oracle Data Block & Recover using RMAN

What is data block corruption?

A database block is corrupted when its content has changed from what Oracle Database expects to find. If not prevented or repaired, block corruption can bring down the database and possibly result in the loss of key business data.

Prerequisite :

• Audiance of this document should be experienced DBA
• Database should be in archive log mode and have good backup

Procedure of work :

• Create tablespace  using sysdba
• create user  using sysdba
• create table  using test user 
• insert records  using test user
• check the header of table segment 
• manually corrupt the block on terminal 
• flush the buffer cahce  as sysdba
• Disconnect and re-connect test user and query the table you will get block corruption error 

To get information about datafiles exists in database .

select file_name from dba_data_files;

create tablespace useing below command :

create tablespace app_corrupt datafile '/u01/app/oracle/oradata/risdev/app_corrupt01.dbf' size 5m;

create user as follow below command

create user test identified by test123
default tablespace app_corrupt
quota unlimited on app_corrupt;

grant create session, resource to test;


Login using test user account :

create emp table :

create table emp(eno number(8)) tablespace app_corrupt;

Insert records in emp table :

begin
for i in 1..1000000
loop
insert into emp values(i);
end loop;
end;


select count(*) from emp;


SQL> SELECT header_block FROM dba_segments WHERE segment_name='EMP';

HEADER_BLOCK
------------
         130

SQL>

Take tablespace backup using RMAN:

backup tablespace app_corrupt;

Mannualy Corrupt the datafile block using below command:

dd of=/u01/app/oracle/oradata/testdb/corrupt_block_test.dbf bs=8192 conv=notrunc seek=130  EOF
testing corruption
EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 0.000133601 s, 142 kB/s


[oracle@localhost ~]$

SQL> select count(*) from emp;
select count(*) from emp
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/risdev/app_corrupt01.dbf'


SQL>

connect with RMAN and validate tablespace :

RMAN> validate tablespace APP_CORRUPT;

Starting validate at 05-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/u01/app/oracle/oradata/risdev/app_corrupt01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1            640             1669173
  File Name: /u01/app/oracle/oradata/risdev/app_corrupt01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              496
  Index      0              0
  Other      1              143

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/risdev/risdev/trace/risdev_ora_24069.trc for details
Finished validate at 05-SEP-19

RMAN>


SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         5        130          1                  0 CORRUPT            0

SQL>


Recover corrupt block using RMAN:

RMAN> blockrecover datafile 5 block 130;

Starting recover at 05-SEP-19
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db/dbs/02ub15f9_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db/dbs/02ub15f9_1_1 tag=TAG20190905T185433
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 05-SEP-19

RMAN>



SQL>
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SQL>

Adding a Datafile/Tempfile to Temp Tablespace

Connect with oracle database and check tempfile location & information :

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 5 14:58:33 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625

SQL>

Add tempfile to temp tablepsace : 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/risdev/temp02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

Tablespace altered.

SQL>



Verify added tempfile : 



SQL> set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;SQL> SQL> SQL> SQL>   2    3    4    5

TABLESPACE FILE_NAME                                             SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
---------- -------------------------------------------------- ---------- --- ---------- ---------------
TEMP       /u01/app/oracle/oradata/risdev/temp01.dbf                  32 YES 32767.9844            .625
TEMP       /u01/app/oracle/oradata/risdev/temp02.dbf                5120 YES      32767            1024

SQL>

* Make sure enough space available at mount point .

Query:

set pages 999
set lines 400
col FILE_NAME format a50
col TABLESPACE_NAME format a10
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#

order by d.TABLESPACE_NAME, d.FILE_NAME;

Find Out Virtual Memory PAGESIZE on Linux

[root@localhost ~]# getconf PAGESIZE
4096
[root@localhost ~]# getconf PAGE_SIZE
4096
[root@localhost ~]#

[root@localhost ~]# getconf -a | grep -i pagesize
PAGESIZE                           4096
[root@localhost ~]#

Flush Bad SQL Plan from Shared Pool

SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

ADDRESS          HASH_VALUE
---------------- ----------
0000000064E29800 1689401402

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

no rows selected

SQL>

PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared

To declare the DBMS_SHARED_POOL.PURGE you have to run dbmspool.sql under ORACLE_HOME/rdbms/admin/  directory

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');
BEGIN DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[oracle@localhost admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 30 16:55:58 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL>

SQL> @?/rdbms/admin/dbmspool.sql

Session altered.

Package created.

Grant succeeded.

Session altered.

SQL>

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

ADDRESS          HASH_VALUE

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

0000000064E29800 1689401402

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

no rows selected

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL>