DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Sunday, September 30, 2018

How to use Goldengate Logdump Utility

Logdump Commands:

Open Logdump
Go to the Oracle GoldenGate Software directory.

[GoldenGate]$ $GG_HOME/logdump

Open a Trail File
To open a trail file and read it’s content, specify the trail file at the logdump prompt. Trail files are usually found in the GoldenGate dirdat directory.

ls -lrt $GG_HOME/dirata
-rw-rw-rw- 1 oracle oinstall 78325 Dec 7 10:38 GG000001
-rw-rw-rw- 1 oracle oinstall 78325 Dec 7 10:42 GG000002
-rw-rw-rw- 1 oracle oinstall 78325 Dec 7 10:55 GG000003
                                             
You can also determine the current trail file directory/name by running the “INFO process_name” command at the ggsci prompt.

Open and view the details of local trail file.

Logdump> OPEN ./dirdat/GG000001
Change the file name and location as required.

Set Output Format
Enable the following options so that you are able to view the results in a readable format in your Logdump sessionL

Set trail file header detail on
The FILEHEADER contains the header details of the currently opened trail file.

Logdump> FILEHEADER DETAIL

Record Header
Logdump> GHDR ON

Set Column Details on
It displays the list of columns, their ID, length, Hex values etc.

Logdump> DETAIL ON

User token details
User token is the user defined information stored in trail, associated with the table mapping statements. The CSN (SCN in Oracle Database) associated with the transaction is available in this section.

Logdump> USERTOKEN DETAIL

Set length of the record to be displayed
In this case it is 128 characters.

Logdump> RECLEN 128
Viewing the Records
To view particular records in the trail files, navigate as below in the local trail file.

First record in the trail file
Here “0” is the beginning of the trial file

Logdump> POS 0
Move to a specific record, at a particular RBA
The “xxxx” is the RBA number.
Logdump> POS xxxx
Next record in the opened trail file
Logdump> N
Or
Logdump> NEXT

Saturday, September 29, 2018

ERROR: ORA-00020: maximum number of processes (1000) exceeded



ORA-00020 this error occure when  database exceeds the initialization parameter PROCESSES value.

   1)The  solution will be to increase the PROCESSES parameter,
   2)kill the the unwanted process like local listener connection which is  known to kill.

Query To Get Concurrent Request ID for a Given Oracle SID




SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&SID')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

How to export/import full Oracle Apex applications

Script for Apex application and workspace export:

#!/bin/sh
#apex application backup

APPBKP=/u01/oracle/appbkp

WRKSPCBKP=/u01/oracle/wrkbkp

JAVA_HOME=/home/oracle/jdk1.7.0_71

cd /u01/TEST/apex/utilities

export CLASSPATH=/u01/TEST/app/oracle/product/12.1.0/db_1/oui/jlib/classes12.jar:

/home/oracle/jdk1.7.0_71/bin/java oracle.apex.APEXExport -db 172.168.25.11:1526:TEST -user system -password <xxxxxxxxx> -instance

mv f*.sql $APPBKP

#Apex workspace backup
/home/oracle/jdk1.7.0_71/bin/java oracle.apex.APEXExport -db 172.168.25.11:1526:TEST -user system -password <xxxxxxxxx> -expWorkspace

mv w*.sql $WRKSPCBKP



Script for Apex application and workspace import:


#!/bin/sh

#change as your apex schema name
#this script should run current directory where as applcation backup dir or workspace dir

cd /u01/backup
ls -ltr *.sql|awk {'print $9'}>hi.txt
 cat hi.txt|while read line
do
sqlplus "/ as sysdba"<<!
alter session set current_schema = APEX_050000;
@$line
exit;
!
done

EBS R12 Front page is not opening

Hi,


After running the following steps issue has been resolved.

  1).cd $FND_TOP/patch/115/bin
2).perl ojspCompile.pl --compile --flush -p 2
3).Run the autoconfig both db and apps tier
4).bringup the service and test the login



verify the status:
$ADMIN_SCRIPTS_HOME/adopmnctl.sh status

ORA-03134: Connections to this server version are no longer supported



12c Db-link cannot connect 9i Database as per the latest upgrade and the compatibility matrix


    
          1) connecting from 12c to 9i Db by adding another 11g Database in-between.
          2)Connecting 12c Db to 9i Db and vis-versa using db-link ( Doc ID 2320287.1 ) 

Java out of memory "java/lang/OutOfMemoryError" in glassfish

Java heap space increase in glass-fish console.

Go to Configurations ---> JVM Setting.

Change  the value  -XX:MaxOermSize=192m to Approximate value
                                -XX:PermSize= 64m to Approximate value
                                





Change the java heap size on Backend:


Go to glassfish config directory :
                                                         1) cd glassfish/domains/domain1/config
                                                                2) Edit the domain.xml find the jvm
                                                                3) Change the value XX:MaxOermSize= ,  -XX:PermSize=              
     





Friday, September 28, 2018

Scripts to check the nologging tables and indexes


col index_name format a50
col table_name format a50
col db_name format a14
col type format a10

select sys_context('userenv', 'db_name') db_name, owner||'.'||table_name table_name, LOGGING, 'TABLE' type
from dba_tables
where LOGGING ='NO'
  and owner not like '%SYS%'
  and owner not in ('DBSNMP', 'XDB')
  and (owner, table_name) not in (select owner, table_name
                                  from dba_external_tables)
union all
select sys_context('userenv', 'db_name') db_name, owner||'.'||index_name index_name, LOGGING, 'INDEX' type
from dba_indexes
where LOGGING ='NO'
  and owner not like '%SYS%'
  and owner not in ('DBSNMP', 'XDB')
order by 1, 2
/

Scripts to bounce the database to Archivelog mode and Flashback mode



set echo off
set feedb off
set heading off
set verify off
set trimspool on
set lines 200
set define on
SET SERVEROUTPUT ON SIZE 1000000

-- variable sid varchar2
-- col sid new_value sid format a12
-- select sys_context('userenv', 'db_name') sid from dual;
-- select '&&sid' from dual;

define spoolfile=${DBADGM}/tmp/${ORACLE_SID}_bounce_db_alfbon.sql

spool &&spoolfile;

DECLARE
    lv_LOG_MODE     VARCHAR2(12)  := NULL;
    lv_FLASHBACK_ON VARCHAR2(18)  := NULL;
    lv_sql          VARCHAR2(400) := NULL;
BEGIN
    SELECT log_mode, flashback_on
      INTO lv_LOG_MODE, lv_FLASHBACK_ON
    FROM V$DATABASE;

    -- DBMS_OUTPUT.PUT_LINE ('connect / as sysdba');
    IF lv_LOG_MODE='NOARCHIVELOG' AND lv_FLASHBACK_ON='NO' THEN
        DBMS_OUTPUT.PUT_LINE ('shutdown immediate');
        DBMS_OUTPUT.PUT_LINE ('startup mount');
        DBMS_OUTPUT.PUT_LINE ('alter database archivelog;');
        DBMS_OUTPUT.PUT_LINE ('alter database flashback on;' );
        DBMS_OUTPUT.PUT_LINE ('alter database open;');
    ELSIF lv_LOG_MODE='NOARCHIVELOG' AND lv_FLASHBACK_ON='YES' THEN
        DBMS_OUTPUT.PUT_LINE ('shutdown immediate');
        DBMS_OUTPUT.PUT_LINE ('startup mount');
        DBMS_OUTPUT.PUT_LINE ('alter database archivelog;');
        DBMS_OUTPUT.PUT_LINE ('alter database open;');
    ELSIF lv_LOG_MODE='ARCHIVELOG' AND lv_FLASHBACK_ON='NO' THEN
        DBMS_OUTPUT.PUT_LINE ('shutdown immediate');
        DBMS_OUTPUT.PUT_LINE ('startup mount');
        DBMS_OUTPUT.PUT_LINE ('alter database flashback on;' );
        DBMS_OUTPUT.PUT_LINE ('alter database open;');
    ELSE
        DBMS_OUTPUT.PUT_LINE ('@database');
        DBMS_OUTPUT.PUT_LINE ('@dg_database');
    END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20900, SQLERRM);
END;
/

spool off

set termout on
set heading on

@&&spoolfile;
!rm &&spoolfile

undefine spoolfile

Buffer Pool used



Summary


You know that you can define the size of each buffer pool in the buffer_cache as you like, but how much of them is used? Use this script first to find how buffer pools are set.
SELECT SUBSTR(NAME, 0, 22) NAME, SUBSTR(display_value, 0,10) VALUE
FROM v$parameter
WHERE NAME IN ('db_keep_cache_size', 'db_recycle_cache_size', 'db_cache_size');

NAME                   VALUE
---------------------- ----------
db_cache_size          30G
db_keep_cache_size     1G
db_recycle_cache_size  1G

The following script shows how much of each pool is used
SELECT DECODE(kcbwbpd.bp_name, 'DEFAULT', 'db_cache_size', 'RECYCLE',
'db_recycle_cache_size', 'KEEP', 'db_keep_cache_size')
buffer_pool_name, ROUND((COUNT(*)*:block_size)/(1024*1024),2) USED_MB
FROM x$kcbwds kcbwds, x$kcbwbpd kcbwbpd , x$bh bh
WHERE kcbwds.set_id >= kcbwbpd.bp_lo_sid
AND kcbwds.set_id <= kcbwbpd.bp_hi_sid
AND kcbwbpd.bp_size != 0
AND kcbwds.addr = bh.set_ds
AND bh.state !=0
GROUP BY kcbwbpd.bp_name;

BUFFER_POOL_NAME         USED_MB
--------------------- ----------
db_cache_size           30358.94
db_recycle_cache_size       2.66
db_keep_cache_size          7.09

Script to drop Standby Logfile


COL MEMBER FORMAT A48

SET SERVEROUTPUT ON SIZE 1000000


DECLARE
    lv_sql_1 VARCHAR2(400) := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ';
    lv_sqL   VARCHAR2(400) := NULL;

    CURSOR c_log IS
        select TO_CHAR(GROUP#) group_no,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE
        from v$logfile
        where type ='STANDBY';

BEGIN
    FOR c_log_rec IN c_log LOOP
        lv_sql := lv_sql_1 ||c_log_rec.group_no;
        EXECUTE IMMEDIATE lv_sql ;
        DBMS_OUTPUT.PUT_LINE(lv_sql);
    END LOOP;
END;
/

Find objects size in MBytes (TABLES, INDEXES, PARTITIONS etc)



Summary

Finding object size in Oracle database is very important and common.
Is it very useful to know the exact size occupied by the object at the tablespace.
The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily .

For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)

SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',
'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
--AND TABLESPACE_NAME LIKE 'COSTE%'
--AND SEGMENT_NAME LIKE 'P2010201%'
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND OWNER = 'TEST_POC'
--AND ROUND(bytes/(1024*1024),2) > 1000
ORDER BY bytes DESC;

You can group by tablespace, owner and segment type and see the total space occupied in MBytes
SELECT tablespace_name, owner, segment_type "Object Type",
       COUNT(owner) "Number of Objects",
       ROUND(SUM(bytes) / 1024 / 1024, 2) "Total Size in MB"
FROM   sys.dba_segments
WHERE  tablespace_name IN ('TEST')
GROUP BY tablespace_name, owner, segment_type
ORDER BY tablespace_name, owner, segment_type;

Script to add new Standby Logfile



col member format a60
set lines 200
set pages 999
set serveroutput on size 1000000

DECLARE
    CURSOR c_inst IS
        SELECT thread#, MIN(GROUP#) min_group, MAX(GROUP#) max_group, sum(members) cnt
        FROM v$log
        GROUP BY thread#
        ORDER BY thread#;

    CURSOR c_log (pTHREAD# IN NUMBER) IS
        SELECT lf.group#, lf.member, l.thread#, l.sequence#, l.bytes/1024/1024 mb, lf.status
        FROM v$logfile lf, v$log l
        WHERE lf.group# = l.group#
          AND l.thread# = pTHREAD#
          -- AND lf.INST_ID = l.INST_ID
          -- AND l.thread# = (SELECT MAX(thread#) FROM gv$log)
        ORDER BY lf.group#, lf.member;

    lv_thread      NUMBER         := 0;
    lv_group_Min   NUMBER         := 0;
    lv_group_Max   NUMBER         := 0;
    lv_group_tot   NUMBER         := 0;
    lv_group_Cur   NUMBER         := 0;
    lv_group_Last  NUMBER         := 0;
    lv_case        NUMBER         := 0;
    lv_members     NUMBER         := 0;
    lv_cnt         NUMBER         := 0;
    lv_redo_seq    VARCHAR2(12)   := NULL;
    lv_debug       BOOLEAN        := FALSE;
    -- lv_debug       BOOLEAN        := TRUE;
    lv_sql         VARCHAR2(4000) := NULL;
    lv_keyword     VARCHAR2(8)    := 'redo';
    lv_last_gp     NUMBER         := 0;
    lv_last_member varchar2(80);
    lv_last_size   NUMBER         := 0;
    lv_last_seq    VARCHAR2(12)   := NULL;

    FUNCTION logfile_count (p_logfile_type VARCHAR2 DEFAULT 'ONLINE')
    RETURN INTEGER
    IS
        lv_cnt INTEGER := 0;
    BEGIN
        SELECT COUNT(*) INTO lv_cnt
        FROM v$logfile
        WHERE type = UPPER(p_logfile_type);    -- 'STANDBY';
        RETURN lv_cnt;

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RETURN 0;
            WHEN OTHERS THEN
                RETURN NULL;
    END logfile_count;
BEGIN
    IF logfile_count ('STANDBY') > 3 THEN
       RETURN;
    END IF;

    BEGIN
        SELECT MAX(GROUP#)
          INTO lv_group_tot
        FROM V$LOGFILE;

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                lv_group_max := 0;
            WHEN OTHERS THEN
                RAISE;
    END;

    FOR c_inst_rec IN c_inst LOOP
           lv_thread := c_inst_rec.thread#;
           lv_group_min := c_inst_rec.min_group;
           lv_group_max := c_inst_rec.max_group;
           lv_members := c_inst_rec.cnt;

        IF lv_debug THEN
            DBMS_OUTPUT.PUT_LINE ('lv_group_min='||TO_CHAR(lv_group_min)||'  lv_group_max='||TO_CHAR(lv_group_max)||
                                  '  lv_members='||TO_CHAR(lv_members));
        END IF;

        lv_sql := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD '||TO_CHAR(lv_thread);
        DBMS_OUTPUT.PUT_LINE (lv_sql);
        IF lv_debug THEN
            DBMS_OUTPUT.PUT_LINE (lv_sql);
        END IF;
        lv_group_cur := lv_group_tot + lv_group_min;

        IF lv_debug THEN
            DBMS_OUTPUT.PUT_LINE ('lv_group_cur='||TO_CHAR(lv_group_cur)||'  lv_group_max='||TO_CHAR(lv_group_max));
        END IF;

        lv_cnt := 0;
        FOR c_log_rec IN c_log (lv_thread) LOOP
            IF lv_debug THEN
                DBMS_OUTPUT.PUT_LINE ('The current group is : '||TO_CHAR(lv_group_cur));
            END IF;

            LV_REDO_SEQ := lv_keyword||LPAD(TO_CHAR(lv_thread), 2, '0')||'_'||LPAD(TO_CHAR(lv_group_cur), 2,'0')||'_sb';

            IF lv_members = 2*(lv_group_max-lv_group_min+1) THEN
              BEGIN
                IF lv_group_last <> c_log_rec.group# THEN
                    IF lv_cnt = 0 THEN
                        DBMS_OUTPUT.PUT_LINE ('  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                           REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                   INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                   lv_redo_seq)||''''||' ) SIZE ' || TO_CHAR(c_log_rec.MB) ||'M REUSE');
                        lv_sql := lv_sql||'  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                                  REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                   INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                          lv_redo_seq)||'''';
                    ELSE
                        DBMS_OUTPUT.PUT_LINE (',  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                            REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                   INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                    lv_redo_seq)||''''||' ) SIZE ' || TO_CHAR(c_log_rec.MB) ||'M REUSE');
                        lv_sql := lv_sql||',  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                                  REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                   INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                          lv_redo_seq)||'''';
                    END IF;
                    lv_group_last := c_log_rec.group#;
                    lv_group_cur := lv_group_cur + 1;
                END IF;
              END;
            ELSIF lv_members = lv_group_max-lv_group_min+1 THEN
              BEGIN
                IF lv_group_last <> c_log_rec.group# THEN
                    IF lv_cnt = 0 THEN
                        DBMS_OUTPUT.PUT_LINE ('  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                           REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                   INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                   lv_redo_seq)||''''||') SIZE ' ||TO_CHAR(c_log_rec.MB) ||'M REUSE');
                        lv_sql := lv_sql||'  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                                  REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                          INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                          lv_redo_seq)||''''||') SIZE ' ||TO_CHAR(c_log_rec.MB) ||'M REUSE';
                    ELSE
                        DBMS_OUTPUT.PUT_LINE (',  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                            REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                    INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                    lv_redo_seq)||''''||') SIZE ' ||TO_CHAR(c_log_rec.MB) ||'M REUSE');
                        lv_sql := lv_sql||',  GROUP '||TO_CHAR(lv_group_cur)||' ('||''''||
                                  REPLACE(c_log_rec.member, lv_keyword||SUBSTR(c_log_rec.member,
                                          INSTR(c_log_rec.member, lv_keyword)+LENGTH(lv_keyword), 2),
                                          lv_redo_seq)||''''||') SIZE ' ||TO_CHAR(c_log_rec.MB) ||'M REUSE';
                    END IF;
                    lv_group_last := c_log_rec.group#;
                    lv_group_cur := lv_group_cur + 1;
                END IF;

                IF lv_debug THEN
                    DBMS_OUTPUT.PUT_LINE ('LV_REDO_SEQ='||TO_CHAR(LV_REDO_SEQ));
                    DBMS_OUTPUT.PUT_LINE ('lv_group_cur='||TO_CHAR(lv_group_cur));
                END IF;

              END;
            END IF;
            LV_CNT := LV_CNT + 1;
            lv_last_gp := lv_group_cur;
            lv_last_member := c_log_rec.member;
            lv_last_size := c_log_rec.MB;
            -- lv_last_seq := lv_keyword||LPAD(TO_CHAR(lv_thread), 2, '0')||'_sb';
            lv_last_seq := lv_keyword||LPAD(TO_CHAR(lv_thread), 2, '0')||'_'||LPAD(TO_CHAR(lv_last_gp), 2,'0')||'_sb';
        END LOOP;
        -- One extra logfile
        DBMS_OUTPUT.PUT_LINE (',  GROUP '||TO_CHAR(lv_last_gp)||' ('||''''|| REPLACE(lv_last_member, lv_keyword||
             SUBSTR(lv_last_member, INSTR(lv_last_member, lv_keyword)+LENGTH(lv_keyword), 2), lv_last_seq)||''''||
             ' ) SIZE ' || TO_CHAR(lv_last_size) ||'M REUSE');
         lv_sql := lv_sql||',  GROUP '||TO_CHAR(lv_last_gp)||' ('||''''|| REPLACE(lv_last_member, lv_keyword||
             SUBSTR(lv_last_member, INSTR(lv_last_member, lv_keyword)+LENGTH(lv_keyword), 2), lv_last_seq)||'''';
        DBMS_OUTPUT.PUT_LINE ('/ ');
        -- DBMS_OUTPUT.PUT_LINE (lv_sql);
        -- EXECUTE IMMEDIATE lv_sql;
    END LOOP;
END;
/

Script to check the Scheduled Jobs in the Database



set lines 200
col owner     format a12
col username  format a12
col job       format a30
col instance  format a12
col last_date format a20
col next_date format a20
col enabled   format a7

ttitle "Scheduled jobs ..." left

SELECT OWNER, JOB_NAME job, TO_CHAR(LAST_START_DATE,'mm/dd/yyyy hh24:mi:ss') last_date,
    TO_CHAR(NEXT_RUN_DATE,'mm/dd/yyyy hh24:mi:ss') next_date, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE ENABLED='TRUE'
-- AND OWNER <> 'SYS'
UNION ALL
SELECT SCHEMA_USER owner, 'dba_jobs -- '||TO_CHAR(job) job, TO_CHAR(LAST_DATE,'mm/dd/yyyy hh24:mi:ss') last_date,
    TO_CHAR(NEXT_DATE,'mm/dd/yyyy hh24:mi:ss') next_date, DECODE(broken , 'Y', 'FALSE', 'TRUE') enabled
FROM DBA_JOBS
ORDER BY 1,2
/

ttitle off

Script to get the Metadata of all Dblinks in the Database



set lines 240
set echo off
set feedb off
set termout off
set heading off
SET VERIFY off
set trimspool on
set long 32766
set longchunksize 120
col l1 format a80
col l2 format a160 newline

define spoolfile=/tmp/metadata_all_dblinks_out.sql
spool &spoolfile

select 'select '||''''||'-- '||owner||'.'||db_link||''''||' from dual; ' l1,
       'select dbms_metadata.get_ddl('||''''||'DB_LINK'||''''||','||
       ''''||db_link||''''||','||''''||owner||''''||') from dual; ' l2
from dba_db_links
order by owner, db_link
/
spool off
set termout on

@&spoolfile

set echo on
set feedb on
set heading on
SET VERIFY on

undefine spoolfile

Script to check the Database status in HTML format


set lines 200
col FIRST_CHANGE# format 999999999999999
col NEXT_CHANGE# format 999999999999999
col name format a60
col member format a70
spool db-current-status.html
set markup html on
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
select HXFIL File_num,substr(HXFNM,1,75) name,FHTYP Type,HXERR Validity,FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;
select count(*) ,fhrba_seq,FHTHR from x$kcvfh group by fhrba_seq,FHTHR;
select count(*) ,fhsta from x$kcvfh group by fhsta;
select count(*) ,FHSCN,FHTHR from x$kcvfh group by FHSCN,FHTHR;
select file#,error from v$datafile_header where length(error)>=1;
select * from gv$log;
select group#,member from v$logfile;
select distinct(status)from v$datafile;
select FILE#,TS# , status, NAME from v$datafile where status not in ('SYSTEM','ONLINE') order by 1;
select count(*) from v$backup where status = 'ACTIVE';
select name,dbid,CONTROLFILE_TYPE,OPEN_RESETLOGS,open_mode,log_mode from v$database;
select DISTINCT (TO_CHAR (CHECKPOINT_TIME)) ,count(*) from V$DATAFILE_HEADER group by CHECKPOINT_TIME;
select * from v$database_incarnation;
set markup html off
spool off

Script to check the Data Guard status



set feedb off
set pages 99

set lines 200

column dbid              format 9999999999 heading "DbId"
column name              format a9 heading "Name"
column open_mode         format a21 heading "Open Mode"
column protection_mode   format a20 heading "Protect Mode"
column protection_level  format a20 heading "Protect Level"
column switchover_status format a18 heading "SwitchOver | Status"
column remote_archive    format a8 heading "Remote|Archive"
column database_role     format a16 heading "Database Role"
column flashback_on      format a5  heading "Flash|Back"
column created           format a19 heading "Created "
column RESETLOGS_TIME    format a19 heading "Resetlogs "
column db_unique_name    format a24 heading "DB Unique Name"

select dbid, db_unique_name, open_mode, database_role,
       -- to_char(created, 'mm/dd/yyyy hh24:mi:ss') created,
       -- to_char(RESETLOGS_TIME, 'mm/dd/yyyy hh24:mi:ss') RESETLOGS_TIME,
       protection_mode, switchover_status,
       flashback_on  /* 10g only */
from v$database
;
set lines 200
col dest_id       format 99 heading "ID"
col archiver      format a6 heading "Archvr"
col transmit_mode format a12 heading "Transmit|Mode"
col affirm        format a3  heading "Aff"
col async_blocks  format 999999 heading "Async|Blocks"
col net_timeout   format 999999 heading "Network|Timeout"
col delay_mins    format 999999 heading "Delay|Minutes"
col reopen_secs   format 999999 heading "Reopen|Seconds"
col register      format a3     heading "Reg"
col binding       format a10    heading "Binding"
col dest_name     format a20    heading "Dest Name"
col destination   format a29    heading "Destination"
col error         format a20    heading "Error"

SELECT dest_id, dest_name, status, protection_mode, destination, srl, error
FROM v$archive_dest_status
WHERE destination is not null    -- and status = 'VALID'
ORDER BY dest_id;

set lines 200
col dest_id       format 99  heading "ID"
col archiver      format a10 heading "Archiver"
col transmit_mode format a12 heading "Transmit|Mode"
col affirm        format a3  heading "Aff"
col async_blocks  format 999999 heading "Async|Blocks"
col net_timeout   format 999999 heading "Network|Timeout"
col delay_mins    format 999999 heading "Delay|Minutes"
col reopen_secs   format 999999 heading "Reopen|Seconds"
col register      format a3     heading "Reg"
col binding       format a10    heading "Binding"
col valid_type    format a15    heading "Valid Type"
col valid_role    format a12    heading "Valid Role"
col valid_now     format a16     heading "Valid now?"

SELECT dest_id, archiver, transmit_mode, affirm, async_blocks,
       net_timeout, delay_mins, reopen_secs, register, binding,
       valid_type, valid_role, valid_now
FROM v$archive_dest
WHERE register = 'YES' OR valid_now = 'YES'
ORDER BY dest_id;

set lines 200

column status            format a12
column time              format a22
column SEVERITY          format a13
column DEST_ID           format 9999  heading "Dest|  ID"
column ERROR_CODE        format 99999 heading "Error| Code"
column TIMESTAMP         format a20
column MESSAGE           format a80 word_wrapped

select PROCESS, status, SEQUENCE#, THREAD#, BLOCK#, BLOCKS,
       TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') TIME
from V$MANAGED_STANDBY;

column message format a93 word_wrapped
select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
  and timestamp between systimestamp -1 and systimestamp
order by timestamp;

set lines 200
col thrd        format 9999          heading "Thrd"
col almax       format 9999999       heading "Last Seq|Received"
col alscn1      format 9999999999999 heading "Received|First-SCN"
col alnscn      format 9999999999999 heading "Received|Next-SCN"
col lhmax       format 9999999       heading "Last Seq|Applied"
col lhscn1      format 9999999999999 heading "Applied|First-SCN"
col lhnscn      format 9999999999999 heading "Applied|Next-SCN"
col log1sttime  format a19           heading "Received 1st Time"
col hist1sttime format a19           heading "Applied 1st Time"
col diff        format 9999          heading "Diff"

select al.thrd, almax, log1sttime, alscn1, alnscn,
       lhmax, hist1sttime, lhscn1, lhnscn, almax-lhmax diff
from (select thread# thrd, max(sequence#) almax, max(FIRST_CHANGE#) alscn1,
      max(next_change#) alnscn, to_char(max(first_time),'yyyy/mm/dd hh24:mi:ss') log1sttime
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax,  max(FIRST_CHANGE#) lhscn1,
        max(next_change#) lhnscn, to_char(max(first_time),'yyyy/mm/dd hh24:mi:ss') hist1sttime
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd(+) = lh.thrd;

col name format a24
col value format a90
select name, value from v$parameter
where name ='log_archive_config' or name like 'dg_broker_%'
   or name like 'fal_%'
order by name;

set feedb on

Script to Show Plan Baseline 



set long 32767
set pages 9999
set lines 140
set verify off

accept sql_id  prompt "Enter the SQL ID : ";
variable sql_id varchar2

COLUMN sql_id     FORMAT A13
COLUMN sql_handle FORMAT A21 newline
COLUMN plan_name  FORMAT A31
COLUMN origin     FORMAT A14
COLUMN enabled    FORMAT A7
COLUMN accepted   FORMAT A8
COLUMN fixed      FORMAT A5
COLUMN autopurge  FORMAT A9
COLUMN CREATED    FORMAT A19
COLUMN sql_text   FORMAT A130 word_wrapped newline heading sql_text
column signature  format 99999999999999999999
column sql_id     format a13

select -- sql_text sql_text,
       s.sql_id, signature, EXECUTIONS,
       round(ELAPSED_TIME/decode(EXECUTIONS, 0, 1, EXECUTIONS),2) ELAPSED_TIME,
       round(CPU_TIME/decode(EXECUTIONS, 0, 1, EXECUTIONS),2) CPU_TIME,
       round(BUFFER_GETS/decode(EXECUTIONS, 0, 1, EXECUTIONS),2) BUFFER_GETS,
       round(DISK_READS/decode(EXECUTIONS, 0, 1, EXECUTIONS),2) DISK_READS, OPTIMIZER_COST,
       sql_handle, plan_name, origin, enabled, accepted, fixed, autopurge,
       to_char(CREATED , 'mm/dd/yyyy hh24:mi:ss') CREATED
from dba_sql_plan_baselines spb,
    (select distinct exact_matching_signature, sql_id
     from v$sql) s
where s.exact_matching_signature (+) = spb.SIGNATURE
  and (s.sql_id like '%&SQL_ID%')
order by CREATED
/

Script to Drop Plan Baseline 



set long 32767
set pages 9999
set lines 200
set serveroutput on size 1000000


accept SQL_Handle prompt "Enter the SQL handle: ";

accept plan_name  prompt "Enter the SQL plan name : ";


DECLARE
    lv_plans       pls_integer;
    lv_sql_handle  VARCHAR2(30) := '&SQL_Handle';
    lv_plan_name   VARCHAR2(30) := '&plan_name';
BEGIN
    lv_plans := DBMS_SPM.drop_SQL_PLAN_BASELINE (
        sql_handle => lv_sql_handle,
        plan_name  => lv_plan_name
    );
    -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(lv_plans));
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

Script to Disable Plan Baseline 



set long 32767
set pages 9999
set lines 200
set serveroutput on size 1000000


accept SQL_Handle prompt "Enter the SQL handle: ";

accept plan_name  prompt "Enter the SQL plan name : ";


DECLARE
    lv_plans       pls_integer;
    lv_sql_handle  VARCHAR2(30) := '&SQL_Handle';
    lv_plan_name   VARCHAR2(30) := '&plan_name';
BEGIN
    lv_plans := DBMS_SPM.alter_SQL_PLAN_BASELINE (
        sql_handle => lv_sql_handle,
        plan_name  => lv_plan_name,
        attribute_name  => 'enabled',
        attribute_value => 'NO'
    );
    DBMS_OUTPUT.PUT_LINE('Plans Altered: '||TO_CHAR(lv_plans));
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

Script to Enable Plan Baseline 



set long 32767
set pages 9999
set lines 200
set serveroutput on size 1000000


accept SQL_Handle prompt "Enter the SQL handle: ";

accept plan_name  prompt "Enter the SQL plan name : ";


DECLARE
    lv_plans       pls_integer;
    lv_sql_handle  VARCHAR2(30) := '&SQL_Handle';
    lv_plan_name   VARCHAR2(30) := '&plan_name';
BEGIN
    lv_plans := DBMS_SPM.alter_SQL_PLAN_BASELINE (
        sql_handle => lv_sql_handle,
        plan_name  => lv_plan_name,
        attribute_name  => 'enabled',
        attribute_value => 'YES'
    );
    DBMS_OUTPUT.PUT_LINE('Plans Altered: '||TO_CHAR(lv_plans));
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

ADOP Fails With Error "PLS-00201: identifier 'AD_JAR.GET_JRIPASSWORDS' must be declared

When applying ADOP patch getting below error.,

AutoPatch error:
ORA-06550: line 3, column 1:
PLS-00201: identifier 'AD_JAR.GET_JRIPASSWORDS' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored

Solution:

Step 1:
Please login as applmgr.
Source the application RUN environment file.

Step 2:

 cd $AD_TOP/patch/115/sql/

check below files are present in the location.,
1.ADJRIS.pls

2. ADJRIB.pls

if these files are present in the above path , Login to database as apps database user and run below 2 SQL's

incase the files are not present in the location then apply patch 1702376 and then re execute the patch.

Weblogic Patching Error Using BSU - "java.lang.OutOfMemoryError: GC overhead limit exceeded"

While applying Weblogic bsu patch manually, we frequently get below error like.,

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
        at java.util.AbstractCollection.toArray(AbstractCollection.java:136)
        at java.util.ArrayList.addAll(ArrayList.java:559)
        at org.apache.xmlbeans.impl.schema.SchemaTypeImpl.getProperties(SchemaTypeImpl.java:705)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.loadPropertyMap(XBeanDataHandler.java:775)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.<init>(XBeanDataHandler.java:99)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.createDataHandler(XBeanDataHandler.java:559)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.getComplexValue(XBeanDataHandler.java:455)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:442)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:464)
        at com.bea.plateng.patch.dao.cat.PatchCatalog.getPatchDependencies(PatchCatalog.java:56)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getInvalidatedPatchMap(PatchCatalogHelper.java:1621)
        at com.bea.plateng.patch.PatchSystem.updatePatchCatalog(PatchSystem.java:436)
        at com.bea.plateng.patch.PatchSystem.refresh(PatchSystem.java:130)
        at com.bea.plateng.patch.PatchSystem.setCacheDir(PatchSystem.java:201)
        at com.bea.plateng.patch.Patch.main(Patch.java:281)

Solution:
Follow these steps to resolve the issue:

1. Go to $FMW_Home/utils/bsu.
2. Edit bsu.sh (for UNIX) or bsu.cmd (for Windows).
3. Here you will find the following: MEM_ARGS="-Xms256m -Xmx512m"
4. Increase these values as needed: for example, to "-Xms2048m -Xmx2048m".

rerun the patch using below command:

bsu.sh -patch_download_dir=$FMW_Home/utils/bsu/cache_dir -prod_dir=$FMW_Home/wlserver_10.3 -patchlist=<Patch name> -verbose -install


Opatch lsinventory or Apply Output Issue Because of Lock File

When performing command  "opatch lsinventory or opatch apply" in oracle RDBMS or FMW home you getting below errors like.,


OracleHomeInventory was not able to create a lock file, probably due to a failed OPatch Session. The loaded inventory might not show correctly what you have in the Oracle Home.
or
[ Error during Oracle Home discovery Phase]. Detail: OPatchSession cannot load inventory for the given Oracle Home /u01/appl/fs1/FMW_Home/oracle_common. Possible causes are:
   No read or write permission to ORACLE_HOME/.patch_storage
   Central Inventory is locked by another OUI instance
   No read permission to Central Inventory
   The lock file exists in ORACLE_HOME/.patch_storage

   The Oracle Home does not exist in Central Inventory

Follow below steps to Solve the issue:
Step 1: Checks all running sessions related to RDBMS & FMW home is killed properly.

Step 2:  Check the "patch_locked" file is exists in "$ORALCE_HOME/.patch_storage/"

Step 3: Remove or rename the "patch_locked" file.

rm $ORALCE_HOME/.patch_storage/patch_locked 
OR
mv $ORALCE_HOME/.patch_storage/patch_locked $ORALCE_HOME/.patch_storage/patch_locked_delete

Then rerun the opatch.

TCPS Configuration for oracle database


Server side wallet
--------------------
======================================================
Server wallet location : /ndevdb/oracle/TEST/wallet
========================================================


orapki wallet create -wallet "/ndevdb/oracle/TEST/wallet" -pwd WalletPasswd123 -auto_login_local

orapki wallet add -wallet "/ndevdb/oracle/TEST/wallet" -pwd WalletPasswd123 \
  -dn "CN='DEVASCP.forbesmarshall.com'" -keysize 1024 -self_signed -validity 3650
 
 
  orapki wallet display -wallet "/ndevdb/oracle/TEST/wallet" -pwd WalletPasswd123
 
 
  orapki wallet export -wallet "/ndevdb/oracle/TEST/wallet" -pwd WalletPasswd123 \
   -dn "CN='DEVASCP.forbesmarshall.com'" -cert /tmp/'DEVASCP.forbesmarshall.com'-certificate.crt
 
 
   DEVASCP.forbesmarshall.com-certificate.crt
 
 
 Client Side Wallet
 --------------------
 ==========================================================
 Client Wallet location : /ndevdb/oracle/TEST/client_wallet
 ===========================================================

 orapki wallet create -wallet "/ndevdb/oracle/TEST/client_wallet" -pwd WalletPasswd123 -auto_login_local


 orapki wallet add -wallet "/ndevdb/oracle/TEST/client_wallet" -pwd WalletPasswd123 -dn "CN=DEVASCP" -keysize 1024 -self_signed -validity 3650


 orapki wallet display -wallet "/ndevdb/oracle/TEST/client_wallet" -pwd WalletPasswd123


 orapki wallet export -wallet "/ndevdb/oracle/TEST/client_wallet" -pwd WalletPasswd123 -dn "CN=DEVASCP" -cert /ndevdb/oracle/TEST/client_wallet/DEVASCP-certificate.crt



 Exchange Certificates
 ----------------------

 orapki wallet add -wallet "/ndevdb/oracle/TEST/client_wallet" -pwd WalletPasswd123 -trusted_cert -cert /tmp/DEVASCP.forbesmarshall.com-certificate.crt




 Load the client certificate into the server wallet.
 ---------------------------------------------------

 orapki wallet add -wallet "/ndevdb/oracle/TEST/wallet" -pwd WalletPasswd123 \
 -trusted_cert -cert /ndevdb/oracle/TEST/client_wallet/DEVASCP-certificate.crt





Grant ACL to user for sending mail



Step – 1 :: create ACL scrip change “principal” name(Schema/User name).

begin
  dbms_network_acl_admin.create_acl(
    acl         => 'utl_smtp.xml',
    description => 'Allow mail to be send',
    principal   => 'USER',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

Step – 2::
begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_smtp.xml',
  principal => 'USER',
  is_grant  => TRUE,
  privilege => 'connect'
  );
  commit;
end;

Step - 3 ::
begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'utl_smtp.xml',
  host => '12.345.67.89',
  lower_port => 25,
  upper_port => 25
  );
  commit;
end;

Create Self-signed certificate using orapki



orapki wallet create -wallet /ndevdb/oracle/TEST/self_signed


orapki wallet add -wallet /devdb/oracle/TEST/self_signed -dn 'CN=TEST,C=US' -keysize 2048 -self_signed -validity 3650


orapki wallet display -wallet /devdb/oracle/TEST/self_signed


orapki wallet export -wallet /devdb/oracle/TEST/self_signed -dn 'CN=TEST,C=US' -cert /devdb/oracle/TEST/self_signed/b64certificate.txt

Steps to create SSL for apex



Refer Doc id

How To Configure SSL For Oracle XML DB ( Doc ID 942976.1 )

How to Configure APEX to Use SSL ( Doc ID 740491.1 )


**********************************************************************

Step 1:

A wallet is required to be able to set up an SSL connection.

Ensure the files ewallet.p12 and cwallet.sso exist in the wallet directory.

Ensure these control parameters exist in the sqlnet configuration files (sqlnet.ora and listener.ora):
When setting these control parameters it is advised to make the edits using Oracle Net Manager

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /ots0/app/oracle/product/11.1.0/network/admin))
)
 
SSL_CLIENT_AUTHENTICATION=FALSE

Verify a secure sqlplus connection succeeds:
 
In the listener.ora open a secure port:
  eg. add address:   (ADDRESS = (PROTOCOL = TCPS)(HOST = nlsu22)(PORT = 1966))
 
In the tnsnames.ora add:
 
v111_s =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCPS) (Host = nlsu22) (Port = 1966) )
  (CONNECT_DATA = (SID = v111) )
)
 
Step 2:
=======

Set dispatcher for TCPS
 
Add the following entry in the database configuration file (init<SID>.ora:):
eg.
dispatchers='(PROTOCOL=TCP)(SERVICE=v111XDB)','(PROTOCOL=TCPS)(SERVICE=v111XDB)'
 
and restart the database instance. Alternatively make the change by means of the alter system command:
   
alter system set dispatchers = '(INDEX=0)(PROTOCOL=TCPS)(SERVICE=v111XDB)', '(INDEX=1)(PROTOCOL=TCP)(SERVICE=v111XDB)' scope=both;
 
You can set the dispatcher for TCPS only as well if desired.


Step 3:
=======

Set http2-port and http2-protocol in the XDB configuration:

See Note 942945.1

Check listener status to verify the ports are defined as endpoints.
This should look like:


STATUS of the LISTENER
------------------------
...
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nlsu22.nl.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nlsu22.nl.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nlsu22.nl.oracle.com)(PORT=2100))(Presentation=FTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=nlsu22.nl.oracle.com)(PORT=1443))(Presentation=HTTP)(Session=RAW))
Services Summary...
...


Step 4:
======

In case of XDB Configuration

set serveroutput on

DECLARE
  l_cfgxml XMLTYPE;
  l_value VARCHAR2(5) := '&secure_port'; -- Secure port#
BEGIN
  l_cfgxml := DBMS_XDB.cfg_get();

  IF l_cfgxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/http2-port') = 0 THEN
  -- Add missing elements.

 SELECT insertChildXML
 (l_cfgxml, '/xdbconfig/sysconfig/protocolconfig/httpconfig', 'http2-port',
  XMLType('<http2-port xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' ||
  l_value ||
  '</http2-port>'),
  'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
 )
 INTO l_cfgxml
 FROM dual;

 SELECT insertChildXML
 (l_cfgxml, '/xdbconfig/sysconfig/protocolconfig/httpconfig', 'http2-protocol',
  XMLType('<http2-protocol xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">tcps</http2-protocol>'),
  'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
 )
 INTO l_cfgxml
 FROM dual;

  DBMS_OUTPUT.put_line('http2 port inserted.');
 ELSE
 -- Update existing element.
  SELECT updateXML
  (
  DBMS_XDB.cfg_get(),
  '/xdbconfig/sysconfig/protocolconfig/httpconfig/http2-port/text()',
   l_value,
   'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
  )
  INTO l_cfgxml
  FROM dual;

 DBMS_OUTPUT.put_line('http2 port updated.');
 DBMS_OUTPUT.put_line('Secure port changed into '||l_value);
END IF;

  DBMS_XDB.cfg_update(l_cfgxml);
  DBMS_XDB.cfg_refresh;
END;
/



Enter the port for the apex

************************************************************************************************

In 11g set port using below query

call dbms_xdb.setListenerEndPoint(2, null, 1443,2);


In 12c dbms_xdb.setListenerEndPoint is replaced by DBMS_XDB_CONFIG.SETLISTENERENDPOINT



Run this query to see the current secure settings in your XDB configuration:



col "Protocol" for a15
col "Port#" for a10

select extractValue(value(x),'/httpconfig/http2-protocol', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Protocol"
,      extractValue(value(x),'/httpconfig/http2-port', 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') "Port#"
from   table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'/xdbconfig/sysconfig/protocolconfig/httpconfig'))) x
/

Query to check when the profile was updated



select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE 
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
order by last_update_date desc, short_name, level_set;

Query to find fast recovery area usage

SELECT (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb,
(SPACE_USED/1024/1024/1024)SPACE_USED_gb,
(SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb,
((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVIALABLE_SPACE,
ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%"
FROM V$RECOVERY_FILE_DEST;

Query to find the High CPU utilizing sessions

SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid               FORMAT 9999             HEADING 'SID'
COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'
COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a20              HEADING 'Session Program' TRUNC
COLUMN session_machine   FORMAT a14              HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN cpu_value         FORMAT 999,999,999,999  HEADING 'CPU'
prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by CPU                       |
prompt +----------------------------------------------------+
SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,14)   session_machine
  , sstat.value          cpu_value
FROM
    v$process  p
  , v$session  s
  , v$sesstat  sstat
  , v$statname statname
WHERE
      p.addr (+)          = s.paddr
  AND s.sid               = sstat.sid
  AND statname.statistic# = sstat.statistic#
  AND statname.name       = 'CPU used by this session'
ORDER BY cpu_value DESC

/

Script to find the child process and all details of an sql

col sql_text for a60 wrap
set verify off
set pagesize 999
set lines 155
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99

select sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/

Script to Flush sql

set serveroutput on
set pagesize 9999
set linesize 155
var name varchar2(50)
accept sql_id -
       prompt 'Enter value for sql_id: '

BEGIN

select address||','||hash_value into :name
from v$sqlarea
where sql_id like '&&sql_id';

dbms_shared_pool.purge(:name,'C',1);

END;
/

undef sql_id
undef name

Script to find statements that have significantly different elapsed time than before

set lines 155
col execs for 999,999,999
col before_etime for 999,990.99
col after_etime for 999,990.99
col before_avg_etime for 999,990.99 head AVG_ETIME_BEFORE
col after_avg_etime for 999,990.99 head AVG_ETIME_AFTER
col min_etime for 999,990.99
col max_etime for 999,990.99
col avg_etime for 999,990.999
col avg_lio for 999,999,990.9
col norm_stddev for 999,990.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, execs, before_avg_etime, after_avg_etime, norm_stddev,
       case when to_number(before_avg_etime) < to_number(after_avg_etime) then 'Slower' else 'Faster' end result
-- select *
from (
select sql_id, sum(execs) execs, sum(before_execs) before_execs, sum(after_execs) after_execs,
       sum(before_avg_etime) before_avg_etime, sum(after_avg_etime) after_avg_etime,
       min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev,
       case when sum(before_avg_etime) > sum(after_avg_etime) then 'Slower' else 'Faster' end better_or_worse
from (
select sql_id,
       period_flag,
       execs,
       avg_etime,
       stddev_etime,
       case when period_flag = 'Before' then execs else 0 end before_execs,
       case when period_flag = 'Before' then avg_etime else 0 end before_avg_etime,
       case when period_flag = 'After' then execs else 0 end after_execs,
       case when period_flag = 'After' then avg_etime else 0 end after_avg_etime
from (
select sql_id, period_flag, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, period_flag, sum(execs) execs, sum(etime)/sum(decode(execs,0,1,execs)) avg_etime from (
select sql_id, 'Before' period_flag,
nvl(executions_delta,0) execs,
(elapsed_time_delta)/1000000 etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and ss.begin_interval_time <= sysdate-&&days_ago
union
select sql_id, 'After' period_flag,
nvl(executions_delta,0) execs,
(elapsed_time_delta)/1000000 etime
-- (elapsed_time_delta)/decode(nvl(executions_delta,0),0,1,executions_delta)/1000000 avg_etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and ss.begin_interval_time > sysdate-&&days_ago
-- and s.snap_id >  7113
)
group by sql_id, period_flag
)
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2)
and max_etime > nvl(to_number('&min_etime'),.1)
)
where result like nvl('&Faster_Slower',result)
order by norm_stddev
/

Script for moving sql profile

set sqlblanklines on

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
version varchar2(3);
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin
 select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error
   'select attr_val as outline_hints '||
   'from dba_sql_profiles p, sqlprof$attr h '||
   'where p.signature = h.signature '||
   'and name like (''&&profile_name'') '||
   'order by attr#'
   bulk collect
   into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error
   'select hint as outline_hints '||
   'from (select p.name, p.signature, p.category, row_number() '||
   '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
   '      extractValue(value(t), ''/hint'') hint '||
   'from sqlobj$data sd, dba_sql_profiles p, '||
   '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
   '                               ''/outline_data/hint''))) t '||
   'where sd.obj_type = 1 '||
   'and p.signature = sd.signature '||
   'and p.name like (''&&profile_name'')) '||
   'order by row_num'
   bulk collect
   into ar_profile_hints;

end if;


/*
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select attr_val as outline_hints
bulk collect
into
ar_profile_hints
from dba_sql_profiles p, sqlprof$attr h
where p.signature = h.signature
and name like ('&&profile_name')
order by attr#;
*/

select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => '&&category'
, name => 'PROFILE_'||'&&sql_id'||'_moved'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&force_matching
);
end;
/

undef profile_name
undef sql_id
undef category
undef force_matching

Query to find day by day redo generation

select trunc(completion_time) rundate ,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" 
from v$archived_log 
group by trunc(completion_time) 
order by 1;

Steps to change dbname using NID

SHUTDOWN IMMEDIATE
STARTUP MOUNT
Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege
nid TARGET=sys/password@TSH1 DBNAME=TSH2

Query To Get Form Personalizations Details

1) Query to Get Form Personalization Details ( Oracle Applications )  from Database.

FND_FORM_CUSTOM_RULES - The Rules for the form customization's. A rule must have 1 more more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.
FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule
FND_FORM - stores information about your registered application forms. Each row includes names (the actual SQL*Forms form name, and the Easy Form form title) and a description of the form. Each row also includes a flag that indicates whether this form is included in the Audit Trail audit set. You need one row for each form in each application. Oracle Application


Select Distinct
    A.Id,
    A.Form_Name ,
    A.Enabled,
    C.User_Form_Name,
    D.Application_Name ,
    A.Description,
    Ca.Action_Type,
    Ca.Enabled,
    Ca.Object_Type,
    ca.message_type,
    ca.message_text
from
    FND_FORM_CUSTOM_RULES a,
    FND_FORM b,
    FND_FORM_TL c,
    Fnd_Application_Tl D,
    Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
    And B.Form_Id = C.Form_Id
    And B.Application_Id = D.Application_Id
    And D.Application_Id = 230 --For Order Management
    And C.User_Form_Name Like 'Inventory%'  --All the Forms that Start with Sales
    And A.Enabled ='Y'
    and a.id = ca.rule_id


SELECT
    ffv.form_id          "Form ID",
    ffv.form_name        "Form Name",
    ffv.user_form_name   "User Form Name",
    ffv.description      "Form Description",
    ffcr.sequence        "Sequence",
    ffcr.description     "Personalization Rule Name"
FROM fnd_form_vl             ffv,
       fnd_form_custom_rules   ffcr
WHERE ffv.form_name = ffcr.form_name
ORDER BY ffv.form_name, ffcr.sequence;



SELECT 
    ffcr.SEQUENCE "Seq", ffcr.description "Description",
    DECODE (ffcr.rule_type,
           'F', 'Form',
            'A', 'Function',
            'Other'
           ) "Level",
    ffcr.enabled "Enabled",
    ffcr.trigger_event "Trigger Event",
    ffcr.trigger_object "Trigger Object",
    ffcr.condition "Condition",
    DECODE (ffcr.fire_in_enter_query,
            'Y', 'Both',
            'N', 'Not in Enter-Query Mode',
            'O', 'Only in Enter-Query Mode',
            'Other'
           ) "Processing Mode"
FROM apps.fnd_form_custom_rules ffcr
WHERE ffcr.function_name = 'PO_POXPOEPO'
    AND ffcr.form_name = 'POXPOEPO'
ORDER BY ffcr.SEQUENCE;