Perform Switchover Operation In Oracle Data Guard.

1. Verify primary database whether it is possible to Switchover .

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE ;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL>
2. Check that there is no active users connected to the databases.
SQL> SELECT DISTINCT OSUSER,USERNAME,STATUS FROM V$SESSION WHERE STATUS = 'ACTIVE';
OSUSER                         USERNAME                       STATUS
------------------------------ ------------------------------ --------
oracle                         CBSUSER                             ACTIVE
oracle                                                        ACTIVE

3. If session are active check any process are active, if process are active inform user to commite the transaction.

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE='USER' AND SID <>(SELECT DISTINCT SID FROM V$MYSTAT);
no rows selected

4. Switch current logfile.

SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL>

5. Initiate the switchover.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.
SQL>

6. Now shutdown Primary database.

SQL> SHUTDOWN IMMEDIATE;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

7. Now connect in primary database and start database in nomount mode.

[oracle@drsdb1 dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 12 12:22:39 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> CONN /AS SYSDBA
Connected to an idle instance.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size                  2228024 bytes
Variable Size             805306568 bytes
Database Buffers          469762048 bytes
Redo Buffers                8769536 bytes

8. Mount database as standby database :

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL>

9. Start the recovery :

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL>
--------------- IN Standby Site (Presently Primary) ---------

10. Cancel Recovery :

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>

11. Use the following  if there is time lag is used.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER
*
ERROR at line 1:
ORA-16177: media recovery is not required

SQL>

12. Now switchover in Standby as Primary database :

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
SQL>

13. Now start the Database

SQL> ALTER DATABASE OPEN;
Database altered.
SQL>

14. Now check Primary (Old Standby ) Database.

SQL> SET LINE 200
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,SWITCHOVER_STATUS FROM V$DATABASE;
NAME      OPEN_MODE            DB_UNIQUE_NAME                 SWITCHOVER_STATUS
--------- -------------------- ------------------------------ --------------------
SBLCBS    READ WRITE           DCDBCBS                         TO STANDBY
SQL>

ORA-03297: file contains used data beyond requested RESIZE value

Cause : Some portion of datafile are using by database Segment which you trying to trim.

Solution : Move / Drop all Segment extends from the region prior that you want to trim.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> SELECT TABLESPACE_NAME
  2  FROM DBA_DATA_FILES
  3  WHERE FILE_NAME ='/u01/app/oracle/oradata/orcln1/tbs_data01.dbf';

TABLESPACE_NAME
------------------------------
TBS_DATA

SQL> SELECT DISTINCT SEGMENT_TYPE,SEGMENT_NAME
  2  FROM DBA_EXTENTS
  3  WHERE TABLESPACE_NAME = 'TBS_DATA';

SEGMENT_TYPE       SEGMENT_NAME
------------------ ---------------------------------------------------------------------------------
TABLE              TEST_RECLAIM_SPACE
INDEX              IND_SERIAL_NO_TEST_RECL

SQL> ALTER TABLE TEST_RECLAIM_SPACE MOVE;

Table altered.

SQL> ALTER INDEX IND_SERIAL_NO_TEST_RECL REBUILD TABLESPACE TBS_DATA;

Index altered.

SQL> purge recyclebin;

Recyclebin purged.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M;

Database altered.

SQL>

Filter String only/Number Only from Set of Strings.

--- Number only from set of strings using TRANSLATE

SELECT TRANSLATE(UPPER('342fgs1dfsN'), '1ABCDEFGHIJKLMNOPQRSTUVWXYZ', '1') a FROM DUAL;

--- String only from set of strings using TRANSLATE

SELECT TRANSLATE('342fgs1dfsN', '0123456789', '1') a FROM DUAL;

--- Number only from set of strings using Regular Expressions

SELECT REGEXP_REPLACE('3NASDasdas','[a-zA-Z'']','')  FROM DUAL;

--- String only from set of strings using Regular Expressions

SELECT REGEXP_REPLACE('3NASDasd5345as','[0-9'']','')  FROM DUAL;

How to Mount New Mount Point In Linux

[root@OEL64N2 ~]# mount -t ext4 /dev/sdb1 /u02
mount: wrong fs type, bad option, bad superblock on /dev/sdb1,
       missing codepage or helper program, or other error
       In some cases useful info is found in syslog - try
       dmesg | tail  or so

[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]# df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/sda5               11G   3.3G   6.9G  33% /
tmpfs                  1.2G    91k   1.2G   1% /dev/shm
/dev/sda2              4.3G   143M   3.9G   4% /tmp
/dev/sda1               13G    12G   345k 100% /u01
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#  mkfs.ext3 /dev/sdb1 2>/dev/null
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
1638400 inodes, 6552504 blocks
327625 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
200 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000

Writing inode tables: done
Creating journal (32768 blocks):
done
Writing superblocks and filesystem accounting information:
done

This filesystem will be automatically checked every 30 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#  mount -t ext4 /dev/sdb1 /u02
[root@OEL64N2 ~]#
[root@OEL64N2 ~]# df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/sda5               11G   3.3G   6.9G  33% /
tmpfs                  1.2G    91k   1.2G   1% /dev/shm
/dev/sda2              4.3G   143M   3.9G   4% /tmp
/dev/sda1               13G    12G   345k 100% /u01
/dev/sdb1               27G   181M    25G   1% /u02
[root@OEL64N2 ~]#

Cost is very few but Query Execution time is very High.

Today I find in my database one query is taking long time to execute, after the investigation I have seen Plan Cost is very low but it's taking huge time to execute.

Problem : In table there are two index both are composite Index. One is composite with two column in which value of one column in only one distinct value within 32 Million and another column contain null value in more then 29 Millions row for that Cardinality was more then 29 Millions and it takes too much long time to execute.

Solution: I have remove this index and Plan taking another index to Execute the Query.

Resolve Archivelog gaps in Data Guard.

In my case one of archive log file are not transfer from primary database to standby database for that standby database fall into archivelog gap.

Solution : I have found missed archivelog file in my primary database (If file are not find then need to take rman backup using the current scn number of standby and applied into standby database) and I have transferred it to standby database, but standby database are not able to resolve this gap for that I have registered this archivelog file using the alter database register logfile 'location of missed archivedlog file'. 


Error from alert log file of standby :

CORRUPTION DETECTED: In redo blocks starting at block 444418count 2048 for thread 1 sequence 1497
RFS[1687]: Possible network disconnect with primary database
Sun Sep 13 21:25:19 2015
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 1497-1497
 DBID 2276360779 branch 885908186
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.

DRHOST1$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 13 21:40:37 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> alter database register logfile '/fra/archivelog/2015_09_13/o1_mf_1_1497_bzbddsjf_.arc';
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Block new session after certain number of session in single schema.

CREATE TABLE SCHEMA_NUMBER_OF_SESSION
(
   USER_NAME           VARCHAR2 (100),
   NUMBER_OF_SESSION   NUMBER (10),
   ALLOWED_SESSION  NUMBER (10)
);

CREATE OR REPLACE TRIGGER TR_LOGON_CHECK
   AFTER LOGON
   ON SCHEMA_NAME.SCHEMA
DECLARE
   V_NUMBER_OF_USER    NUMBER;
   V_ALLOWED_SESSION   NUMBER;
BEGIN
   SELECT NUMBER_OF_SESSION, ALLOWED_SESSION
     INTO V_NUMBER_OF_USER, V_ALLOWED_SESSION
     FROM SCHEMA_NUMBER_OF_SESSION
    WHERE USER_NAME = USER;

   IF V_NUMBER_OF_USER >= V_ALLOWED_SESSION
   THEN
      RAISE_APPLICATION_ERROR (-20100, 'New Session are not allowed.');
   ELSE
      UPDATE SCHEMA_NUMBER_OF_SESSION
         SET NUMBER_OF_SESSION = NUMBER_OF_SESSION + 1
       WHERE USER_NAME = USER;
   END IF;
 
END TR_LOGON_CHECK;


CREATE OR REPLACE TRIGGER TR_LOGOFF_UPDATE
   BEFORE LOGOFF
   ON SCHEMA_NAME.SCHEMA
DECLARE
   V_NUMBER_OF_USER    NUMBER;
   V_ALLOWED_SESSION   NUMBER;
BEGIN
   UPDATE SCHEMA_NUMBER_OF_SESSION
      SET NUMBER_OF_SESSION = NUMBER_OF_SESSION - 1
    WHERE USER_NAME = USER;
END TR_LOGOFF_UPDATE;

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>

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>

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>

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>

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.

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>

ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

Cause :  This condition may be temporary, such as at instance startup. In my case instance are in nomount mode and I am trying to connect database through Primary database.

Solution : After adding SID_LIST_LISTENER in listener I have solved this problem.

SQL> col DESTINATION format a35
col ERROR format a65
set lines 130
set pages 100
SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;
SQL> SQL> SQL> SQL>
DESTINATION                         ERROR
----------------------------------- -----------------------------------------------------------------
USE_DB_RECOVERY_FILE_DEST
cbsdb                              ORA-12528: TNS:listener: all appropriate instances are blocking
                                    new connections

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount;
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
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
DCDB1$
DCDB1$
DCDB1$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-JUL-2015 13:58:33

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DCDB1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date                26-MAR-2015 14:39:25
Uptime                    120 days 23 hr. 19 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/app/diag/tnslsnr/DCDB1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=en4_bootip_server1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cbsdb" has 1 instance(s).
  Instance "cbsdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
DCDB1$ cat /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DCDB1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/oracle/app

DCDB1$ echo $ORACLE_HOME
/u01/oracle/app/product/11.2.0/db_1
DCDB1$ echo $ORACLE_SID
cbsdb
DCDB1$ vi listener.ora
"listener.ora" 22 lines, 544 characters # listener.ora Network Configuration File: /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DCDB1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
(GLOBAL_DBNAME = cbsdb)
(ORACLE_HOME = /u01/oracle/app/product/11.2.0/db_1)
(SID_NAME = cbsdb)
    )
  )
ADR_BASE_LISTENER = /u01/oracle/app

~
~"listener.ora" 22 lines, 544 characters
:wq
"listener.ora" 22 lines, 540 characters
DCDB1$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-JUL-2015 14:03:47

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting /u01/oracle/app/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/app/diag/tnslsnr/DCDB1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=en4_bootip_server1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DCDB1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date                25-JUL-2015 14:03:55
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/app/diag/tnslsnr/DCDB1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=en4_bootip_server1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cbsdb" has 1 instance(s).
  Instance "cbsdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit

FAILED DESTINATION Switchover Status In Data Guard.

Cause: This status shown when invalid configuration exist in data guard parameter or Primary Server are not able to connect Standby Server.

Solve :  In my case primary server listeners are blocking new connection. After starting my

SQL> select name,open_mode,db_unique_name,switchover_status from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME
--------- -------------------- ------------------------------
SWITCHOVER_STATUS
--------------------
SBLCBS    READ WRITE           drsbldb
FAILED DESTINATION

SQL> col DESTINATION format a35
col ERROR format a65
set lines 130
set pages 100
SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;
SQL> SQL> SQL> SQL>
DESTINATION                         ERROR
----------------------------------- -----------------------------------------------------------------
USE_DB_RECOVERY_FILE_DEST
sblcbs                              ORA-12528: TNS:listener: all appropriate instances are blocking
                                    new connections

Jobs are not working/running .....

CBSDB1$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 28 23:06:10 2015

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

SQL> conn /as sysdba
Connected.
SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL> alter system set job_queue_processes=1000 scope=both;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CBSDB1$

Export Dump File Using Procedure.

CREATE OR REPLACE PROCEDURE SP_DUMP_EXPORT (
P_FROM_DATE DATE,
P_TO_DATE DATE
)
IS
  L_DP_HANDLE       NUMBER;
  L_LAST_JOB_STATE  VARCHAR2(30) := 'UNDEFINED';
  L_JOB_STATE       VARCHAR2(30) := 'UNDEFINED';
  L_STS             KU$_STATUS;

  SCHEMA_NAMES VARCHAR2(300);
BEGIN

    SELECT '''' || REPLACE (WM_CONCAT (USERNAME), ',', ''',''') || ''''
      INTO SCHEMA_NAMES
      FROM DBA_USERS
     WHERE USERNAME IN ('RND', 'HR');

  L_DP_HANDLE := DBMS_DATAPUMP.OPEN(
    OPERATION   => 'EXPORT',
    JOB_MODE    => 'SCHEMA',
    REMOTE_LINK => NULL,
    JOB_NAME    => 'MIG_'||TO_CHAR(P_FROM_DATE,'DD_MM_YYYY')||'_'||TO_CHAR(P_TO_DATE,'DD_MM_YYYY'),
    VERSION     => 'LATEST');

  DBMS_DATAPUMP.ADD_FILE(
    HANDLE    => L_DP_HANDLE,
    FILENAME  => 'MIG_'||TO_CHAR(P_FROM_DATE,'DD_MM_YYYY')||'_'||TO_CHAR(P_TO_DATE,'DD_MM_YYYY')||'.DMP',
    DIRECTORY => 'DATA_DIR');

  DBMS_DATAPUMP.ADD_FILE(
    HANDLE    => L_DP_HANDLE,
    FILENAME  => 'MIG_'||TO_CHAR(P_FROM_DATE,'DD_MM_YYYY')||'_'||TO_CHAR(P_TO_DATE,'DD_MM_YYYY')||'.LOG',
    DIRECTORY => 'DATA_DIR',
    FILETYPE  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 
   DBMS_DATAPUMP.METADATA_FILTER(L_DP_HANDLE, 'SCHEMA_EXPR', 'IN (' || SCHEMA_NAMES || ')');

  DBMS_DATAPUMP.START_JOB(L_DP_HANDLE);

  DBMS_DATAPUMP.DETACH(L_DP_HANDLE);
END;


SQL> EXEC SP_DUMP_EXPORT('01-JAN-2015','30-JAN-2015');

PL/SQL procedure successfully completed.

SQL>

Return data from second query if first query result is empty.

This query help you to use WHEN NO_DATA_FOUND Exception feature in SQL. Here I am used three query.

1. Return result only from first query if first query return some data.
2. Return result only from Second query if first query return no data.
3. Return result only from Third query if first and second query  return no data.

Setup :

CREATE TABLE ACC_PROD
(PROD NUMBER,
 ACC NUMBER,
 RS NUMBER);

Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (1, 0, 1);
Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (0, 101, 1);
Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (0, 0, 1);
COMMIT;

Query :

WITH DATA_FIRST

     AS (SELECT *

           FROM ACC_PROD

          WHERE (PROD = 0 AND ACC = :P_ACC)),

     DATA_SECOND

     AS (SELECT *

           FROM ACC_PROD

          WHERE (PROD = :P_PROD AND ACC = 0)),

     DATA_THIRD

     AS (SELECT *

           FROM ACC_PROD

          WHERE (PROD = 0 AND ACC = 0))

SELECT * FROM DATA_FIRST

UNION ALL

SELECT *

  FROM DATA_SECOND

 WHERE NOT EXISTS (SELECT NULL FROM DATA_FIRST)

UNION ALL

SELECT *

  FROM DATA_THIRD

 WHERE     NOT EXISTS (SELECT NULL FROM DATA_FIRST)

       AND NOT EXISTS (SELECT NULL FROM DATA_SECOND)

Finding Operating Process With Oracle Process across by Session ID.

========== Query =========

set line 200
col sid format 999999
col username format a20
col osuser format a15
col PROGRAM format a15
col terminal format a15
col module format a15

select p.spid os_process_id,
       s.sid session_id,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et,
       p.program,
       p.terminal,
       logon_time,
       module,
       s.osuser
from v$session s, v$process p
where s.paddr= p.addr
and p.spid='&spid'
order by p.spid;

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

SQL> set line 200
col sid format 999999
col username format a20
col osuser format a15
col PROGRAM format a15
col terminal format a15
col module format a15
SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> select p.spid os_process_id,
  2         s.sid session_id,
  3         s.serial#,
  4         s.username,
  5         s.status,
  6         s.last_call_et,
  7         p.program,
  8         p.terminal,
  9         logon_time,
 10         module,
 11         s.osuser
 12  from v$session s, v$process p
 13  where s.paddr= p.addr
 14  and p.spid='&spid'
 15  order by p.spid;
Enter value for spid: 11403790
old  14: and p.spid='&spid'
new  14: and p.spid='11403790'

OS_PROCESS_ID            SESSION_ID    SERIAL# USERNAME             STATUS   LAST_CALL_ET PROGRAM         TERMINAL        LOGON_TIM MODULE          OSUSER
------------------------ ---------- ---------- -------------------- -------- ------------ --------------- --------------- --------- --------------- ---------------
11403790                       4574      28565 PRODUSR              ACTIVE             39 oracle@DBSRV1    UNKNOWN         22-JUL-15 JDBC Thin Client


SQL>

Enable Block Change Tracking.

From Oracle 10.2 onward, Oracle provide block change tracking feature which is very useful to reduce RMAN incremental backup time. Prior this version oracle need to read every block and if the block has changed it was backup, for that RMAN backup job took long time.


SQL> SET LINESIZE    180;
COLUMN filename      FORMAT a40       HEAD 'File Name';
COLUMN status        FORMAT a20         HEAD 'File Status';SQL> SQL>
SQL> select filename,status from v$block_change_tracking;

File Name                                File Status
---------------------------------------- --------------------
                                         DISABLED

SQL> alter database enable block change tracking using file '/u01/app/oracle/oradata/sblcbs/block_change_file01.dbf';

Database altered.

SQL> select filename,status from v$block_change_tracking;

File Name                                File Status
---------------------------------------- --------------------
/u01/app/oracle/oradata/sblcbs/block_cha ENABLED
nge_file01.dbf


SQL>

------ Disabled Block Change Tracking ------

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> select filename,status from v$block_change_tracking;

File Name                                File Status
---------------------------------------- --------------------
                                         DISABLED

SQL>

RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

Cause: Oracle password file does not exist in $ORACLE_HOME/dbs directory of AUXILIARY (Standby) Database.

Solution : Copy password file from primary database directory ($ORACLE_HOME/dbs)  to AUXILIARY (Standby) Database  directory ($ORACLE_HOME/dbs).

[oracle@bcbldb01 dbs]$ rman TARGET sys/oracle@DCCBSDB AUXILIARY sys/oracle@DCCBSDBDR

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 9 17:23:34 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DCCBSDB (DBID=2308296244)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges


[oracle@drdb1 dbs]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall 1536 Jun 17 13:30 orapwdccbsdb
-rw-r--r-- 1 oracle oinstall 1776 Jul  9 17:02 initdccbsdbdr.ora
-rw-rw---- 1 oracle oinstall 1544 Jul  9 17:21 hc_dccbsdbdr.dat
[oracle@drdb1 dbs]$ mv orapwdccbsdb orapwdccbsdbdr
[oracle@drdb1 dbs]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall 1536 Jun 17 13:30 orapwdccbsdbdr
-rw-r--r-- 1 oracle oinstall 1776 Jul  9 17:02 initdccbsdbdr.ora
-rw-rw---- 1 oracle oinstall 1544 Jul  9 17:21 hc_dccbsdbdr.dat
[oracle@drdb1 dbs]$ mv orapwdccbsdb orapwdccbsdbdr


[oracle@bcbldb01 dbs]$ rman TARGET sys/oracle@DCCBSDB AUXILIARY sys/oracle@DCCBSDBDR

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 9 17:35:34 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DCCBSDB (DBID=2308296244)
connected to auxiliary database: DCCBSDB (not mounted)

ORA-10458: standby database requires recovery

Cause: Standby database recovery not completed. In my case, standby database was stopped cause of power failure, for that archive log file was not transmit to standby server. I have found log gap in my primary database by using the following query :

SELECT INST_ID,
     SEQUENCE#,
     TO_CHAR (COMPLETION_TIME, 'DD-MM-YYYY HH24:MI:SS') "Time",
     NAME,
     STATUS,
     ARCHIVED,
     STANDBY_DEST,
     APPLIED
FROM GV$ARCHIVED_LOG
WHERE TO_CHAR (COMPLETION_TIME, 'DD-MM-YYYY') =TO_CHAR (SYSDATE, 'DD-MM-YYYY')
ORDER BY SEQUENCE#;

Solution: I have switch log file in my primary database, standby server receives archive log file and applies all of the archive logs. If the problem still exists Resolve GAP By Apply RMAN Incremental Backup.

The primary database is not able to shift redo data to standby database for that standby database are required to recover. You need to identify this reason. The following query helps you why primary DB is not shifting the redo.

You may check the following two post also.

http://rajiboracle.blogspot.com/2017/05/resolve-standby-gaps-by-applying.html

http://rajiboracle.blogspot.com/2015/09/resolve-archivelog-gaps-in-data-guard.html

col DESTINATION format a35
col ERROR format a65
set lines 130
set pages 100
SELECT INST_ID, DESTINATION, ERROR FROM GV$ARCHIVE_DEST

WHERE DESTINATION IS NOT NULL;

[oracle@drdb1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 13 13:59:50 2015

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

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2228024 bytes
Variable Size             805306568 bytes
Database Buffers          469762048 bytes
Redo Buffers                8769536 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/sblcbsdr/system01.dbf'


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/sblcbsdr/system01.dbf'

In Primary
---------------------------------------
[oracle@dcdb01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 13 14:06:08 2015

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

SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

In Standby 
---------------------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1585093

ORA-01172: recovery of thread 1 stuck at block 2438 of file 2

Cause: This error usually means that blocks of file 2 are corrupted, In this case we need to recover this blocks.

Solution : We need to recover all of corrupted block by using data file recovery or Database recovery. In my case I have recover database.

1. Data file recover.

SQL> recover datafile 2;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>

2. Database Recovery

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
========================================================================

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size                  1219136 bytes
Variable Size             301991360 bytes
Database Buffers          922746880 bytes
Redo Buffers               15556608 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 2438 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
 

RMAN-04006: error from auxiliary database

[oracle@bcbldb01 ~]$ rman TARGET sys/oracle@SBLCBS AUXILIARY sys/oracle@SBLCBSDR
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 5 18:21:40 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SBLCBS (DBID=2308296244)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied
[oracle@bcbldb01 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsblcbs oracle@172.31.36.69:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwsblcbsdr
oracle@172.31.36.69's password:
orapwsblcbs                                                                                                                           100% 1536     1.5KB/s   00:00
[oracle@bcbldb01 ~]$ rman TARGET sys/oracle@SBLCBS AUXILIARY sys/oracle@SBLCBSDR
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 5 18:21:59 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SBLCBS (DBID=2308296244)
connected to auxiliary database: SBLCBS (not mounted)
RMAN>

Deleting Archivelog.

RMAN>DELETE ARCHIVELOG ALL;

RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';

RMAN>DELETE ARCHIVELOG FROM TIME 'SYSDATE-1';

RMAN>DELETE ARCHIVELOG FROM TIME 'SYSDATE-2' UNTIL TIME 'SYSDATE-1';

RMAN>DELETE ARCHIVELOG FROM SEQUENCE 100;

RMAN>DELETE ARCHIVELOG UNTIL SEQUENCE 100;

RMAN>DELETE ARCHIVELOG FROM SEQUENCE 100 UNTIL SEQUENCE 150;

Reclaiming Unused Space

At the time of table creation there is no extend allocate for this table, when table data are increasing/inserting new extend are allocating for this table and extend are not leave until table are :

1. drop
2. Truncate
3. Move table to same/another tablespace.

After deleting data from table space are not release, because when we delete data from the table block will go onto the freelist for that table only, for that another table/extend are not able to use this free blocks and space are not usable by another table/extends.

Now question is how we can Reclaim this free space ?

1. Move Tablespace.
2. Export and import table by using data pump.
3. Redefine table using DBMS_REDEFINITION
4. Create new table, drop existing table, rename new table [Must create existing index in new table].
5. Drop table [If there is no data and table are not required in future ].
6. Truncate table [if Table data are not required].

SQL> CREATE TABLESPACE TBS_DATA DATAFILE
  2    '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' SIZE 500M AUTOEXTEND OFF;

Tablespace created.

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           510976          0             0

SQL>
--- There is no extends in this tablespace, so total space of this tablespace are free.
SQL> CREATE TABLE TEST_RECLAIM_SPACE
  2  (SERIAL_NO NUMBER,
  3   DATA1     VARCHAR2(4000),
  4   DATA2     VARCHAR2(4000),
  5   DATA3     VARCHAR2(4000)
  6  ) TABLESPACE TBS_DATA;

Table created.

SQL> INSERT /*+append*/ INTO TEST_RECLAIM_SPACE
  2  SELECT SERIAL_NO, DATA||DATA||DATA||DATA||DATA||DATA DATA1, DATA||DATA||DATA||DATA||DATA||DATA DATA2,
  3   DATA||DATA||DATA||DATA||DATA||DATA DATA3
  4  FROM(
  5  SELECT LEVEL SERIAL_NO, 'CREATE TABLE TEST_RECLAIM_SPACE
  6  (SERIAL_NO NUMBER,
  7   DATA1     VARCHAR2(4000),
  8   DATA2     VARCHAR2(4000),
  9   DATA3     VARCHAR2(4000)
 10  ) TABLESPACE TBS_DATA
 11  ' DATA
 12  FROM DUAL
 13  CONNECT BY LEVEL <= 100000);

100000 rows created.

SQL>  SELECT A.TABLESPACE_NAME,
  2          (SELECT NVL(SUM (BYTES) / 1024,0)
  3             FROM DBA_FREE_SPACE
  4            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5             FREE_SPACE,
  6          (SELECT NVL(SUM (BYTES) / 1024,0)
  7             FROM DBA_EXTENTS
  8            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9             USED_SPACE,
 10          (SELECT COUNT (*)
 11             FROM DBA_EXTENTS
 12            WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13             TOTAL_EXTENTS
 14     FROM DBA_TABLESPACES A
 15    WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           101376     409600           121

SQL> DELETE FROM TEST_RECLAIM_SPACE;

100000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM TEST_RECLAIM_SPACE;

  COUNT(*)
----------
         0

SQL>

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           101376     409600           121

SQL> ALTER TABLE TEST_RECLAIM_SPACE MOVE;

Table altered.

SQL> SELECT A.TABLESPACE_NAME,
  2         (SELECT NVL(SUM (BYTES) / 1024,0)
  3            FROM DBA_FREE_SPACE
  4           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  5            FREE_SPACE,
  6         (SELECT NVL(SUM (BYTES) / 1024,0)
  7            FROM DBA_EXTENTS
  8           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
  9            USED_SPACE,
 10         (SELECT COUNT (*)
 11            FROM DBA_EXTENTS
 12           WHERE TABLESPACE_NAME = A.TABLESPACE_NAME)
 13            TOTAL_EXTENTS
 14    FROM DBA_TABLESPACES A
 15   WHERE TABLESPACE_NAME = 'TBS_DATA';

TABLESPACE_NAME                FREE_SPACE USED_SPACE TOTAL_EXTENTS
------------------------------ ---------- ---------- -------------
TBS_DATA                           510912         64             1

SQL>