Thursday, August 20, 2015

Convert Multiple Column Value In Row

In hear I am trying to show how we can convert multiple (Three) column  value in single column row.

Table :

CREATE TABLE DATA1
(
  ID    NUMBER,
  COL1  NUMBER,
  COL2  NUMBER,
  COL3  NUMBER

);

INSERT INTO DATA1
   (ID, COL1, COL2, COL3)
 VALUES
   (1, 1, 1, 2);
INSERT INTO DATA1
   (ID, COL1, COL2, COL3)
 VALUES
   (2, 2, 1, 2);
INSERT INTO DATA1
   (ID, COL1, COL2, COL3)
 VALUES
   (3, 5, 2, 3);

SQL> select * from DATA1;

        ID       COL1       COL2       COL3
---------- ---------- ---------- ----------
         1          1          1          2
         2          2          1          2
         3          5          2          3

SQL>

SQL> WITH TABLE_DATA AS (SELECT * FROM DATA1),
  2       DATA
  3       AS (SELECT LEVEL UQID
  4                 FROM DUAL
  5           CONNECT BY LEVEL <= 3),
  6       T_DATA
  7       AS (SELECT UQID,
  8                    ID,
  9                    COL1,
 10                    COL2,
 11                    COL3
 12               FROM TABLE_DATA, DATA
 13           ORDER BY ID),
 14  FINAL_DATA AS(
 15  SELECT ID,
 16         (SELECT (CASE
 17                     WHEN F.UQID = 1 THEN COL1
 18                     WHEN F.UQID = 2 THEN COL2
 19                     WHEN F.UQID = 3 THEN COL3
 20                     ELSE NULL
 21                  END)
 22            FROM T_DATA F
 23           WHERE F.ID = T.ID AND F.UQID = T.UQID)
 24            ROW_VALUE
 25    FROM T_DATA T)
 26  SELECT * FROM FINAL_DATA;

        ID  ROW_VALUE
---------- ----------
         1          1
         1          1
         1          2
         2          2
         2          1
         2          2
         3          5
         3          2
         3          3

9 rows selected.

SQL>

------- This query show duplicate value in multiple column ....

SQL> WITH TABLE_DATA AS (SELECT * FROM DATA1),
  2       DATA
  3       AS (SELECT LEVEL UQID
  4                 FROM DUAL
  5           CONNECT BY LEVEL <= 3),
  6       T_DATA
  7       AS (SELECT UQID,
  8                    ID,
  9                    COL1,
 10                    COL2,
 11                    COL3
 12               FROM TABLE_DATA, DATA
 13           ORDER BY ID),
 14  FINAL_DATA AS(
 15  SELECT ID,
 16         (SELECT (CASE
 17                     WHEN F.UQID = 1 THEN COL1
 18                     WHEN F.UQID = 2 THEN COL2
 19                     WHEN F.UQID = 3 THEN COL3
                   ELSE NULL
 20   21                  END)
 22            FROM T_DATA F
 23           WHERE F.ID = T.ID AND F.UQID = T.UQID)
 24            ROW_VALUE
 25    FROM T_DATA T)
 26  SELECT ID, ROW_VALUE, COUNT(*) FROM FINAL_DATA
 27  GROUP BY ID, ROW_VALUE
 28  HAVING COUNT(*)>1;

        ID  ROW_VALUE   COUNT(*)
---------- ---------- ----------
         1          1          2
         2          2          2

SQL>

Friday, August 14, 2015

ORA-00845: MEMORY_TARGET not supported on this system

Cause : This error comes up because of  Automatic Memory Management (AMM) feature of Oracle 11g R2 seems that shared memory file system (shmfs) is not big enough.

Solution : Increase the size of that file system by issuing the following command.

mount -t tmpfs shmfs -o size=12g /dev/shm

[oracle@OEL64N2 workshops]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 15 01:04:51 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit
Disconnected
[oracle@OEL64N2 workshops]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda5             9.9G  3.1G  6.4G  33% /
tmpfs                 1.2G  695M  434M  62% /dev/shm
/dev/sda2             4.0G  139M  3.7G   4% /tmp
/dev/sda1              12G  6.4G  4.9G  57% /u01
[oracle@OEL64N2 workshops]$ mount -t tmpfs shmfs -o size=12g /dev/shm
mount: only root can do that
[oracle@OEL64N2 workshops]$ su root
Password:
[root@OEL64N2 workshops]#
[root@OEL64N2 workshops]#
[root@OEL64N2 workshops]# mount -t tmpfs shmfs -o size=12g /dev/shm
[root@OEL64N2 workshops]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda5             9.9G  3.1G  6.4G  33% /
tmpfs                  12G     0   12G   0% /dev/shm
/dev/sda2             4.0G  139M  3.7G   4% /tmp
/dev/sda1              12G  6.4G  4.9G  57% /u01
shmfs                  12G     0   12G   0% /dev/shm
[root@OEL64N2 workshops]# su oracle
[oracle@OEL64N2 workshops]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 15 01:08:30 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             805309824 bytes
Database Buffers           25165824 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.
SQL>

Tuesday, August 11, 2015

GATHER_STATS_JOB encountered errors.

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /u01/oracle/app/diag/rdbms/sblcbs/sblcbs/trace/sblcbs_j001_6292190.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"

Cause : Temporary Datapump external tables have not been cleaned up properly.

Solution : The dictionary information should have been dropped when the Data Pump jobs completed.

Find all external table using this query and drop if Data Pump jobs completed.

SELECT OWNER,
       OBJECT_NAME,
       OBJECT_TYPE,
       STATUS
  FROM DBA_OBJECTS
 WHERE OBJECT_NAME LIKE 'ET$%'


SQL> col OBJECT_NAME format a30
SQL>
SQL> SELECT OWNER,
       OBJECT_NAME,
       OBJECT_TYPE,
       STATUS
  FROM DBA_OBJECTS
 WHERE OBJECT_NAME LIKE 'ET$%'  2    3    4    5    6  ;
OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------------------ ------------------- -------
CBSPROD                        ET$0C0900F50001                TABLE               VALID
SQL>
SQL> DROP TABLE CBSPROD.ET$0C0900F50001 PURGE;
Table dropped.
SQL>

Monday, August 10, 2015

ORA-01012: not logged on at instance startup.

Caused : An unfathered shared memory segment still exists from previous startup instance.

Solution : Remove the shared memory segment from OS using sysresv and ipcrm -m <problem shared memory id>.

DCDB1$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 24 10:48:15 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-01012: not logged on
SQL> exit
Disconnected
DCDB1$ sysresv
IPC Resources for ORACLE_SID "sblcbs" :
Shared Memory:
ID              KEY
28311559        0xffffffff
28311558        0xffffffff
29360133        0x4681b78c
Oracle Instance alive for sid "sblcbs"
DCDB1$ ipcrm -m 28311559
DCDB1$ ipcrm -m 28311558
DCDB1$ ipcrm -m 29360133

DCDB1$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 24 10:51:53 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 6.8413E+10 bytes
Fixed Size                  2238616 bytes
Variable Size            5.3687E+10 bytes
Database Buffers         1.4630E+10 bytes
Redo Buffers               93618176 bytes
Database mounted.
Database opened.

Using Automatic Database Diagnostic Monitor (ADDM).

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance problem. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem.

--- Finding All Snapshot List

SQL> SELECT SNAP_ID,
  SNAP_LEVEL,BEGIN_INTERVAL_TIME,
  TO_CHAR(BEGIN_INTERVAL_TIME, 'dd/mm/yy hh24:mi:ss') BEGIN
FROM
   DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID desc;  2    3    4    5    6
   SNAP_ID SNAP_LEVEL BEGIN_INTERVAL_TIME                                                         BEGIN
---------- ---------- --------------------------------------------------------------------------- -----------------
        19          1 03-APR-15 03.11.48.885 PM                                                   03/04/15 15:11:48
        18          1 03-APR-15 03.00.55.000 PM                                                   03/04/15 15:00:55
        17          1 03-APR-15 02.06.34.519 AM                                                   03/04/15 02:06:34
        16          1 03-APR-15 02.00.19.724 AM                                                   03/04/15 02:00:19
        15          1 03-APR-15 01.00.16.811 AM                                                   03/04/15 01:00:16
        14          1 03-APR-15 12.00.13.144 AM                                                   03/04/15 00:00:13
        13          1 02-APR-15 11.10.11.140 PM                                                   02/04/15 23:10:11
        12          1 02-APR-15 10.59.19.000 PM                                                   02/04/15 22:59:19
8 rows selected.

--- Executing Oracle Package DBMS_ADVISOR to Generate advisory task.

SQL> BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
  2    3    4    5      task_name         => '12_15_AWR_SNAP',
  6      task_desc         => 'Advisor for snapshots 12 to 15.');
  7
  8    -- Set the start and end snapshots.
  9    DBMS_ADVISOR.set_task_parameter (
 10      task_name => '12_15_AWR_SNAP',
 11      parameter => 'START_SNAPSHOT',
 12      value     => 12);
 13
 14    DBMS_ADVISOR.set_task_parameter (
 15      task_name => '12_15_AWR_SNAP',
 16      parameter => 'END_SNAPSHOT',
 17      value     => 15);
 18
 19    -- Execute the task.
 20    DBMS_ADVISOR.execute_task(task_name => '12_15_AWR_SNAP');
 21  END;
 22  /
PL/SQL procedure successfully completed.

------ Showing ADDM Report data.

SQL> SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('12_15_AWR_SNAP') AS report
FROM   dual;
SET PAGESIZE 24SQL> SQL>   2
REPORT
--------------------------------------------------------------------------------
          ADDM Report for Task '12_15_AWR_SNAP'
          -------------------------------------
Analysis Period
---------------
AWR snapshot range from 12 to 15.
Time period starts at 02-APR-15 11.10.11 PM
Time period ends at 03-APR-15 02.00.20 AM
Analysis Target
---------------
Database 'ORCLN1' with DB ID 1237928617.
Database version 11.2.0.3.0.
Analysis was requested for all instances, but ADDM analyzed instance orcln1,
numbered 1 and hosted at OEL64BN1.
See the "Additional Information" section for more information on the requested
instances.
Activity During the Analysis Period
-----------------------------------
Total database time was 128 seconds.
The average number of active sessions was .01.
ADDM analyzed 1 of the requested 1 instances.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          Additional Information
          ----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
The database's maintenance windows were active during 99% of the analysis
period.
SQL>

Friday, August 7, 2015

mod_plsql: /apex/wwv_flow.accept HTTP-404 ORA-06550

mod_plsql: /apex/wwv_flow.accept HTTP-404 ORA-06550: line 9, column 3:\nPLS-00201: identifier 'DBMS_UTILITY' must be declared\nORA-06550: line 9, column 3:\nPL/SQL: Statement ignored\n

Cause : This error I have found apex log directory (E:\oracle\FRHome_1\Apache\Apache\logs). APEX_PUBLIC_USER was not able to use DBMS_UTILITY Package.


Solution : GRANT EXECUTE ON DBMS_UTILITY TO APEX_PUBLIC_USER From sys account.

Monday, August 3, 2015

Resize the Redo log file.

We can not resize the redo log file. We must drop and recreate redo log file. When we want to drop redo log file it must be in INACTIVE, if redo log file status are not INACTIVE we have to change the status after dropped from database it will update in control file and we need to remove operating system file manually.

Forcing Checkpoint : Forcing a checkpoint are ensure that all changed in the database buffer cache are written in datafile by using ALTER SYSTEM CHECKPOINT GLOBAL;

[oracle@OEL64BN1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 3 16:08:18 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdbda
Connected.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  49752428800 YES INACTIVE
 2  49652428800 YES INACTIVE
 3  49852428800 NO  CURRENT
SQL>  alter system checkpoint global ;
System altered.
SQL> select sum(bytes)/1024/1024 file_size, group# group_number from v$log group by GROUP#;
 FILE_SIZE GROUP_NUMBER
---------- ------------
50      1
50      2
50      3
SQL> col member format a35
col member format a65
set lines 130
set pages 100
select group#,type, member from v$logfile;SQL> SQL> SQL> SQL>
    GROUP# TYPE    MEMBER
---------- ------- -----------------------------------------------------------------
 3 ONLINE  /u01/app/oracle/oradata/orcln1/redo03.log
 2 ONLINE  /u01/app/oracle/oradata/orcln1/redo02.log
 1 ONLINE  /u01/app/oracle/oradata/orcln1/redo01.log
3 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> !
[oracle@OEL64BN1 ~]$ rm /u01/app/oracle/oradata/orcln1/redo01.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcln1/redo01.log' SIZE 1G;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> exit    !
[oracle@OEL64BN1 ~]$ exitrm /u01/app/oracle/oradata/orcln1/redo01.log.log2.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1    0 1073741824 YES UNUSED
 3  49852428800 NO  CURRENT
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 '/u01/app/oracle/oradata/orcln1/redo02.log' SIZE 1G;
Database altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1    0 1073741824 YES UNUSED
 2    0 1073741824 YES UNUSED
 3  49852428800 NO  CURRENT
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 NO  CURRENT
 2    0 1073741824 YES UNUSED
 3  498 52428800 YES ACTIVE
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 NO  CURRENT
 3  49852428800 YES INACTIVE
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> !
[oracle@OEL64BN1 ~]$ rm /u01/app/oracle/oradata/orcln1/redo02.log.log3.log
[oracle@OEL64BN1 ~]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 '/u01/app/oracle/oradata/orcln1/redo03.log' SIZE 1G;
Database altered.
SQL>  SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 NO  CURRENT
 3    0 1073741824 YES UNUSED
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> SELECT GROUP#,SEQUENCE#,BYTES,ARCHIVED,STATUS FROM V$LOG;
    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
 1  499 1073741824 YES INACTIVE
 2  500 1073741824 YES INACTIVE
 3  501 1073741824 NO  CURRENT
SQL> select sum(bytes)/1024/1024 file_size, group# group_number from v$log group by GROUP#;
 FILE_SIZE GROUP_NUMBER
---------- ------------
      1024      1
      1024      2
      1024      3

log file switch (checkpoint incomplete)

Checkpoint : When LGWR (Log Writer) need to switch next log file, it asks DBWR(DB Writer) to flash dirty (Updated) block into the disk this is checkpoint.

log file switch (checkpoint incomplete): Oracle can't reuse a log file if all modified blocks are not flashed onto the disk, it will wait until flash modified blocks by the previous checkpoint.

Cause : If there are few/ too small redo logs and system produces enough redo to cycle through all the logs before DBWR has been able to complete the checkpoint, then increase the size or number of redo logs.

             If DBWR performance slow due to System I/O.

Solution : If few/small redo logs increase the redo logs, analyze DBWR performance In my case redo was small size.


SQL> SET LINE 200
SQL> col INSTANCE_NAME FORMAT A10
SQL> col SESSION_INFO FORMAT A15
SQL> col EVENT FORMAT A40
SQL> col WAIT_CLASS FORMAT A20
SQL> col WAIT_TIME FORMAT 99999999
SQL>
SQL> SELECT I.INSTANCE_NAME, W.SID||':'||S.SERIAL# SESSION_INFO, W.EVENT, W.WAIT_CLASS_ID, W.WAIT_CLASS, W.WAIT_TIME
  2  FROM GV$SESSION_WAIT W, GV$SESSION S, GV$SQL Q, GV$INSTANCE I
  3  WHERE W.INST_ID=S.INST_ID
  4  AND S.INST_ID=Q.INST_ID
  5  AND I.INST_ID=Q.INST_ID
  6  AND W.SID=S.SID
  7  AND S.SQL_ID=Q.SQL_ID
  8  AND W.WAIT_TIME =0
AND W.EVENT<>'SQL*Net message from client'
AND W.WAIT_CLASS<>'Idle'
  9   10   11  ORDER BY W.SID;
INSTANCE_N SESSION_INFO    EVENT                                    WAIT_CLASS_ID WAIT_CLASS           WAIT_TIME
---------- --------------- ---------------------------------------- ------------- -------------------- ---------
spftldb    3408:1595       log file switch (checkpoint incomplete)     3290255840 Configuration                0
spftldb    5666:895        log file switch (checkpoint incomplete)     3290255840 Configuration                0
spftldb    5666:895        log file switch (checkpoint incomplete)     3290255840 Configuration                0
spftldb    5666:895        log file switch (checkpoint incomplete)     3290255840 Configuration                0
SQL>

ORA-19502: write error on file "/oradata/cbsdb/REDO/redo01a.log", block number 1351681 (block size=512)

Cause : In my case there are no available space in file system. After increase disk space file was created. 


SQL> ALTER DATABASE ADD LOGFILE GROUP 1
('/oradata/cbsdb/REDO/redo01a.log',
'/u01/app/oracle/fast_recovery_area/redo01b.log'
)
size 1024M;  2    3    4    5
ALTER DATABASE ADD LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19502: write error on file "/oradata/cbsdb/REDO/redo01a.log", block number 1351681 (block size=512)
ORA-27072: File I/O error
Additional information: 4
Additional information: 1351681
Additional information: 122368

[oracle@DB REDO]$ exit
exit
SQL> ALTER DATABASE ADD LOGFILE GROUP 1
('/oradata/spftldb/REDO/redo01a.log',
'/u01/app/oracle/fast_recovery_area/redo01b.log'
)
size 1024M;  2    3    4    5
Database altered.
SQL>