Friday, August 26, 2016

How can we see the Bind variable values when they are not in Timestamp and display the cursor plan

We can see the bind variable values with the below query.

 select * from table(dbms_xplan.display_cursor('4b5c6cp4gkup6',null,'+peeked_binds'));


How to display the cursor plan:

Run the cursor and execute the below sql:

select * from table(dbms_xplan.display_cursor);


Tuesday, June 21, 2016

Resetting the same old password in oracle or resolving password cannot be reused ORA-28007: or ORA-28003: password verification failed in oracle

Note:Password resetting is user own decision as per requirement.
Not to be copy pasted from this article and should not be used in production without CR.
This steps are described below keeping developement or perf testing environment in mind.

You might be doing this but still getting the above error:

SQL> select USER#,NAME,PASSWORD FROM USER$ where name='IPPADM' ;

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        26 IPPADM                         C42D1F57A9936520

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

Then you might be setting the password to the older value;

alter user IPPADM identified by values 'C42D1F57A9936520';

not working same message.


select * from dba_profiles where profile='DEFAULT' and  RESOURCE_NAME in ('PASSWORD_REUSE_TIME') ;

alter profile DEFAULT limit PASSWORD_REUSE_TIME UNLIMITED;


alter user IPPADM identified by values 'C42D1F57A9936520';

No help not working.

Finally execute the following:

How to set password expiry to unlimited for a schema account in the DB and reset with the same old password:

select profile from dba_users where username='IPPADM'

DEFAULT

Check the different resource and limit for this profile.

select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME Like 'PASS%'

PASSWORD_LIFE_TIME
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
PASSWORD_VERIFY_FUNCTION
PASSWORD_LOCK_TIME
PASSWORD_GRACE_TIME

Change all the profile limit to unlimited including PASSWORD_VERIFY_FUNCTION which should be set to null.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited;

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;




How to set a samll amount for account lock time for a DB user:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 0.001;



How to change the password expiry to unlimited for a service account:

Check the current status of the user say oracle.


[root@pdevspdb ~]# chage -l oracle
Last password change                                    : May 04, 2016
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 99999
Number of days of warning before password expires       : 7


chage -I -1 -m 0 -M 99999 -E -1 username

Monday, June 20, 2016

How to do a proper cleanup of Oracle Stream configuration for a fresh install of Oracle Streams:

How to do a proper cleanup of Oracle stream configuration for a fresh install of Oracle Streams:


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

EXEC DBMS_APPLY_ADM.STOP_APPLY (APPLY_NAME => 'APPLY_STREAM');

EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE (CAPTURE_NAME => 'CAPTURE_STREAM');

begin
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(PROPAGATION_name  => 'pdsmar_TO_nixar');
end;
/

exec DBMS_PROPAGATION_ADM.DROP_PROPAGATION(propagation_name => 'pdsmar_TO_nixar',drop_unused_rule_sets => TRUE);

EXEC DBMS_CAPTURE_ADM.DROP_CAPTURE(CAPTURE_NAME=>'CAPTURE_STREAM',drop_unused_rule_sets => TRUE);

EXEC DBMS_APPLY_ADM.DROP_APPLY(APPLY_NAME=>'APPLY_STREAM',drop_unused_rule_sets => TRUE);

connect / as sysdba

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.APPLY_q',cascade=>true,drop_unused_queue_table=>true);

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.CAPTURE_q',cascade=>true,drop_unused_queue_table=>true);


drop user STRMADMIN cascade;


----
If above steps  required repeated for multiple environments we can write something line tis:

 BEGIN

    DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

  EXCEPTION
   WHEN OTHERS THEN



     -- Drop the APPLY queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_APPLY_Q1',cascade=>true,drop_unused_queue_table=>true);

     -- Drop the CAPTURE queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_CAPTURE_Q1',cascade=>true,drop_unused_queue_table=>true);
  END;
/

drop user STRMADMIN cascade;


Tuesday, May 31, 2016

Resolving issues for Oracle_Home is not set for Toad even Oracle client is available

Most often after installing Toad developer use to face this problem while starting Toad for Oracle.

Resolution:
Install Oracle 32 bit client on your machine for the same OS if Toad is below version 11.


Install Oracle 64 bit client in your machine for the same OS if Toad version is atleast 11.0...

Wednesday, May 18, 2016

How to use external table to copy table data in a file to a defined directory location in Oracle

External table is sometimes very useful to copy table data based on condition into a file to a defined directory location in Oracle:

CREATE OR REPLACE DIRECTORY  DP_DIR AS  '/tmp/oracle/';


GRANT READ, WRITE ON DIRECTORY SYS.DP_DIR TO TAPADM WITH GRANT OPTION;


Below will create the file;


CREATE TABLE TAPADM.TAP_COM_EVENT_DET_EXT
  ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY DP_DIR
     LOCATION ('TAP_company_event_detail_xt.dmp')
   )
   AS select * from TAPADM.TAP_COMPANY_EVT_DETAIL where company_evt_detail_seq in(select COMPANY_EVT_DETAIL_SEQ from TAPADM.PTED_DIFF_1);


Now to copy the data to some other location at new DB from the above file we can use the below command or syntax.

CREATE TABLE TAPADM.TAP_COM_EVENT_DET_EXT
(
  COMPANY_EVENT_DETAIL_SEQ  VARCHAR2(255 CHAR),
  VERSION                   NUMBER(19),
  CREATOR_ID                VARCHAR2(30 CHAR),
  CREATED_DATE              TIMESTAMP(6),
  MODIFIER_ID               VARCHAR2(30 CHAR),
  MODIFIED_DATE             TIMESTAMP(6),
  REALM_ID                  NUMBER(19),
  EVENT_DETAIL_SUBTYPE      VARCHAR2(100 CHAR),
  COMPANY_FK                VARCHAR2(255 CHAR)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY DP_DIR
     ACCESS PARAMETERS 
       (  )
     LOCATION (DP_DIR:'TAP_company_event_detail_xt.dmp')
  )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;




Monday, May 16, 2016

Resolving PLS-00905: object GGATE.DDLAUX is invalid while installing Golden Gate in 11gR2


PLS-00905: object GGATE.DDLAUX is invalid


SQL> @ddl_setup

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.











Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
1464/9                                   PL/SQL: SQL Statement ignored
1466/25                                  PL/SQL: ORA-00942: table or view does not exist
1475/9                                   PL/SQL: SQL Statement ignored
1477/25                                  PL/SQL: ORA-00942: table or view does not exist
1489/9                                   PL/SQL: SQL Statement ignored
1491/25                                  PL/SQL: ORA-00942: table or view does not exist
1496/9                                   PL/SQL: SQL Statement ignored
1498/25                                  PL/SQL: ORA-00942: table or view does not exist
1503/9                                   PL/SQL: SQL Statement ignored
1505/25                                  PL/SQL: ORA-00942: table or view does not exist
1510/9                                   PL/SQL: SQL Statement ignored

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
1512/25                                  PL/SQL: ORA-00942: table or view does not exist
1595/4                                   PL/SQL: SQL Statement ignored
1596/20                                  PL/SQL: ORA-00942: table or view does not exist
1598/4                                   PL/SQL: SQL Statement ignored
1599/20                                  PL/SQL: ORA-00942: table or view does not exist
1614/27                                  PL/SQL: ORA-00942: table or view does not exist
1614/4                                   PL/SQL: SQL Statement ignored
1616/27                                  PL/SQL: ORA-00942: table or view does not exist
1616/4                                   PL/SQL: SQL Statement ignored


DDL IGNORE TABLE
-----------------------------------
FAILED: Table does not exist

DDL IGNORE LOG TABLE
-----------------------------------
FAILED: Table does not exist

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
0/0                                      PL/SQL: Compilation unit analysis terminated
1/23                                     PLS-00304: cannot compile body of 'DDLAUX' without its
                                         specification

1/23                                     PLS-00905: object GGATE.DDLAUX is invalid


SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
FAILED: Table does not exist

DDL HISTORY TABLE(1)
-----------------------------------
FAILED: Table does not exist

DDL DUMP TABLES
-----------------------------------
FAILED: Table does not exist

DDL DUMP COLUMNS
-----------------------------------
FAILED: Table does not exist

DDL DUMP LOG GROUPS
-----------------------------------
FAILED: Table does not exist

DDL DUMP PARTITIONS
-----------------------------------
FAILED: Table does not exist

DDL DUMP PRIMARY KEYS
-----------------------------------
FAILED: Table does not exist

DDL SEQUENCE
-----------------------------------
FAILED: Sequence does not exist

GGS_TEMP_COLS
-----------------------------------
FAILED: Table does not exist

GGS_TEMP_UK
-----------------------------------
FAILED: Table does not exist

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
126/9                                    PL/SQL: SQL Statement ignored
128/25                                   PL/SQL: ORA-00942: table or view does not exist
133/23                                   PL/SQL: ORA-02289: sequence does not exist
133/5                                    PL/SQL: SQL Statement ignored
707/16                                   PLS-00905: object GGATE.DDLAUX is invalid
707/5                                    PL/SQL: Statement ignored
992/27                                   PL/SQL: ORA-00942: table or view does not exist
992/4                                    PL/SQL: SQL Statement ignored


DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist



LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/pspqa03/pspqa03/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)

Script complete.
SQL>
SQL>
SQL>
SQL> grant create any table to ggs_user;
grant create any table to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
grant create any view to ggs_user;
SQL> grant create any view to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any procedure to ggs_user;
grant create any procedure to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any sequence to ggs_user;
grant create any sequence to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any index to ggs_user;
grant create any index to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any trigger to ggs_user;

grant create any trigger to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL> SQL> grant create any view to ggs_user;
grant create any view to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL>
SQL> @ddl_remove

DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggate
Working, please wait ...
Spooling to file ddl_remove_spool.txt

Script complete.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> grant create any table to ggate;

grant create any view to ggate;

grant create any procedure to ggate;

grant create any sequence to ggate;

grant create any index to ggate;

grant create any trigger to ggate;

grant create any view to ggate;
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>

Grant succeeded.

SQL>
SQL>
SQL>
SQL>
SQL> @ddl_setup

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.











Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/pspqa03/pspqa03/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

then run the other script:

SQL> @role_setup

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> GRANT GGS_GGSUSER_ROLE TO GGATE;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.

Sunday, February 7, 2016

How to view the Checkpoint activites in Oracle Database


How to check to how many times you have to scan the short and long tables.
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN ('table scans (short tables)',
'table scans (long tables)')
/
Try to reduce the number by creating proper table indexes. Note that the count for 'Long Tables scan' must be very small.


How to view the Checkpoint activites
Query the V$SYSSTAT directory view for the checkpoint process activity to monitor the checkpoint process.
SQL> SELECT name, value
FROM v$sysstat
WHERE name like 'background check%'
/
If the "background check started" value is greater than the "background check completed" value, you should increase the size of the REDO LOG files.


How to monitor REDO log file parallel write
Query the V$SYSTEM_EVENT directory view to see the Log File Parallel Write Event to monitor and tune a Redo Log file.
SQL> SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event = 'log file parallel write'
/
The "Waits" column indicates a possible I/O problem.

How to create a ASM instance in RAC

How to create a ASM instance in RAC:
 Step1.    Start cluster synchronization service:
        >crs_start ora.cssd
                     >crs_stat –t (to check the status)
Step2.   Register ASM instance to OCR
>export ORACLE_SID=+ASM
>srvctl add asm -p $ORACLE_HOME/dbs/init+ASM.ora
              > Start the ASM instance any way you like:
              > srvctl start asm
        or
        > sqlplus "/ as sysasm"
        startup
        and check the status
        > srvctl status asm

Init+ASM.ora contents:
instance_type=asm
remote_login_passwordfile=exclusive
Step3.    Create Disk groups
Two Raw devices d100 and d103 of size 165GB and 20GB respectively allocated to ASM.

> sqlplus "/ as sysasm"
CREATE DISKGROUP DATA_AB01 EXTERNAL REDUNDANCY DISK '/dev/ad/rdsk/d101';

CREATE DISKGROUP FRA_AB01 EXTERNAL REDUNDANCY DISK '/dev/ad/rdsk/d102';

How to install and create an ASM disks in Oracle RAC system:-

How to install and create an ASM disks in Oracle RAC system:-

All the steps mentioned are as per my own test cases.
Pl make sure to test for your own environment:

Download the latest Oracle ASM RPMs from http://otn.oracle.com/tech/linux/asmlib/index.html.

Make sure that you download the right ASM driver for your kernel (UP or SMP). 

To install the ASM RPMs on a UP server, run: 
su - root
rpm -Uvh oracleasm-2.4.21-EL-1.0.0-1.i686.rpm \
         oracleasm-support-1.0.2-1.i386.rpm \
         oracleasmlib-1.0.0-1.i386.rpm
To install the ASM RPMs on a SMP server, run: 
su - root
rpm -Uvh oracleasm-2.4.21-EL-smp-1.0.0-1.i686.rpm \
         oracleasm-support-1.0.2-1.i386.rpm \
         oracleasmlib-1.0.0-1.i386.rpm

Configuring and Loading ASM

To load the ASM driver oracleams.o and to mount the ASM driver filesystem, enter: 
su - root
# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
                                                                                                                          
This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.
                                                                                                                          
Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration            [  OK  ]
Creating /dev/oracleasm mount point                        [  OK  ]
Loading module "oracleasm"                                 [  OK  ]
Mounting ASMlib driver filesystem                          [  OK  ]

Scanning system for ASM disks                              [  OK  ]

#


Creating ASM Disks

NOTE: Creating ASM disks is done on one RAC node! The following commands should only be executed on one RAC node!

I executed the following commands to create my ASM disks: (make sure to change the device names!)
(In this example I used partitions (/dev/sda2, /dev/sda3, /dev/sda5) instead of whole disks (/dev/sda, /dev/sdb, /dev/sdc,...)) 
su - root
# /etc/init.d/oracleasm createdisk VOL1 /dev/<sd??>
Marking disk "/dev/sda2" as an ASM disk                    [  OK  ]
# /etc/init.d/oracleasm createdisk VOL2 /dev/<sd??>
Marking disk "/dev/sda3" as an ASM disk                    [  OK  ]
# /etc/init.d/oracleasm createdisk VOL3 /dev/<sd??>
Marking disk "/dev/sda5" as an ASM disk                    [  OK  ]
#
# # Replace "sd??" with the name of your device. I used /dev/sda2, /dev/sda3, and /dev/sda5

To list all ASM disks, enter: 
# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
#

On all other RAC nodes, you just need to notify the system about the new ASM disks: 
su - root
# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks                              [  OK  ]
#

 

Monday, February 1, 2016

How to get or extract year,month,day fom date or timestamp field of a table in oracle


How to extract year,month,day  from timestamp or date field of a table in oracle:
 Here service start date is the column name:
 
   SELECT EXTRACT(YEAR FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL

2009
2010
2011
2012
2010
   
   SELECT min(EXTRACT(YEAR FROM service_start_date)) FROM apcadm.apc_info_service where service_start_date IS NOT NULL 


1998
getting a count wise report:

 SELECT count(info_fk), EXTRACT(YEAR FROM service_start_date) FROM   apcadm.apc_info_service where service_start_date IS NOT NULL
   group by EXTRACT(YEAR FROM service_start_date)
   order by 2;

Same way we can extract the month number and day of the month:

 SELECT EXTRACT(month FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL
  
   SELECT EXTRACT(day FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL

Tuesday, January 26, 2016

How to compile invalid objects in oracle

SELECT CASE object_type
  2         WHEN 'PACKAGE' THEN
  3          'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
  4         ELSE
  5          'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
  6         END
  7    FROM dba_objects
  8   WHERE STATUS = 'INVALID'
  9    and owner = 'TESTCMS'
 10     AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');

CASEOBJECT_TYPEWHEN'PACKAGE'THEN'ALTER'||OBJECT_TYPE||''||OWNER||'.'||OBJECT_NAM
--------------------------------------------------------------------------------
ALTER PACKAGE TESTCMS.BCE_DOWNLOAD_LIST_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_ITEM_DISPLAY_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_EDIT_CONTENT_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_IMAGE_GALLERY_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_RSS_FEED COMPILE BODY;

For function and procedure use:
alter function TESTCMS.object_name compile;
alter trigger schema.object_type compile;
alter procedure schema.object_type compile;

alter trigger PORTAL.WWDOC_DOCU_BRU_TRG compile;

=====================
TESTCMS                        BCE_CMS_NOTIFICATION_PORTLET                                                                                                     PACKAGE BODY
TESTCMS                        BCE_CMS_APPROVAL                                                                                                                 PACKAGE BODY

ALTER PACKAGE TESTCMS.BCE_CMS_NOTIFICATION_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_APPROVAL COMPILE BODY;

Oracle Text indexes synchronisation

PURPOSE=======
To show how the Oracle Text indexes are synchronized and how to control it. 

SCOPE & APPLICATION===================
This note is for Oracle Text administrators and developers. 

Synchronizing Text Indexes
A text index always responds within the same transaction to DELETE in the indexed column, but the response to INSERT and UPDATE which is modeled as a DELETE followed by an INSERT is asynchronous. An internal trigger which isinvisible to the customer requests index maintenance by inserting a row intoa queue table in CTXSYS schema for each new row in the indexed column.This DML queue can be tracked by querying the CTX_PENDING or CTX_USER_PENDINGText data dictionary view. 
Example: 
  create table docs (id number primary key, doc varchar2(60));
  create index i_doc on docs (doc) indextype is ctxsys.context; 
  insert into docs values (1,'A man walking on the street with a barking dog.'); 
  select * from ctx_user_pending; 
  PND_INDEX_NAME                 PND_ROWID          PND_TIMES   ------------------------------ ------------------ ---------   I_DOC                          AAAHVoAAEAAAEiqAAA 14-NOV-06 
  select * from docs where contains(doc,'dog')>0; 
  no rows selected 
In this situation the table and the text index on a column of this table are notsynchronized. A text query on the indexed column does not return the new row eventhough the new document fulfills the condition. So the table and the index have to be synchronized. The above defined DML queue contains information about rows in the document tableto be synchronized with the text index. We say that the table is synchronized withits text index when there is no entry referencing to this text index in the DML queue. 
After synchronizing the table and its index text queries return with the new/updateddocuments fulfilling the condition. Of course, the entry referencing the rows to besynchronized disappear from the DML queue. 
Example: 
  select * from docs where contains(doc,'man')>0; 
         ID DOC   --------- ----------------------------------------------------------------           1 A man walking on the street with a barking dog 
  select * from ctx_user_pending; 
  no rows selected 
There are two approaches for synchronization: 
  1. Explicit one-shot request for a given index   2. Implicit continuous request for all indexes database-wide 
1. Explicit Synchronization 
Explicit synchronization can be implemented using ctx_ddl.sync_index PL/SQL procedure.
  begin   ctx_ddl.sync_index(idx_name => '<index_name>', memory => '50M');  end;
The memory parameter determines the maximum memory which will be allocated by theprocess which synchronizes the table and the index. Its default and maximum valueis determined by the DEFAULT_INDEX_MEMORY and MAX_INDEX_MEMORY system parameterrespectively which can be queried from CTX_PARAMETERS Text data dictionary viewand can be modified with ctx_adm.set_parameter PL/SQL procedure. It is worth setting larger values for faster completion and there will be fewer recordsper token in the token table. 

PL/SQL procedures have to be written for implementing synchronization in order to start jobs running automatically. Defining jobs requires at least one job queue process running. 
Example: 
  create or replace procedure my_index_sync   is   begin     ctx_ddl.sync_index( idx_name => 'my_index', memory => '50M');   end;   / 
  declare   v_job number;     begin    dbms_job.submit( job=>v_job, what=>'my_index_sync;', next_date=>sysdate, interval=>'sysdate+1/24');  end;   / 
One of the advantages of this method is to make differences between indexes,i.e. less important indexes can be syncronized rarely and more important often.So synchronization policy can be implemented for each text index. On the other hand, DBA always has to keep track the job system and if a new text index is created in the database DBA has to look after its synchronization. 

2. Explicit Synchronization for all text indexes database-wide 
This is a short script that runs sync in a loop with a variable delay.It remains logged on, continually scanning for DML to do on any index.
Note that this script will never exit on its own.

create table stop_sync(aa varchar(10)); declare  the_count integer;  sleep_time number := 5;begin  loop    -- scan and process pending dml to do    ctx_ddl.sync_index('null');    select count(*) into the_count from stop_sync;    exit when the_count > 0;    -- sleep for 5 seconds to avoid spinning    dbms_lock.sleep(sleep_time);  end loop;  delete from stop_sync;  commit;end;/
To stop it, you simply insert a row into the table "stop_sync" from another session.
insert into stop_sync values ('X');commit;


Monday, January 18, 2016

How to check which are the object cached in memory

 How to check which are the object cached in memory:-

select SUBSTR(owner,1,10) Owner,SUBSTR(Type,1,12) Type,SUBSTR(name,1,20) Name,
executions,sharable_mem Mem_used,
SUBSTR(Kept||' ',1,4) "Kept"
from v$db_object_cache
where type in('trigger','procedure','Package Body','Package')
order by executions desc;

Sunday, January 17, 2016

How would an application developer execute a query based on specific partition

What is interval partitioning:


 Oracle Database 11g does, with a feature called Interval Partitioning. Here, you don't define partitions and their boundaries but merely an interval that defines each partition's boundaries. Here is the same example in interval partitioning:

create table sales6
(
   sales_id    number,
   sales_dt    date
)
partition by range (sales_dt)
                              
interval (numtoyminterval(1,'MONTH'))
(
   partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);

How would an application developer address a specific partition? One way is to know the name, which may not be possible, and even if you know, it is highly error prone. To facilitate the access to specific partition, Oracle Database 11g offers a new syntax for partitioning SQLs:

SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy'));

  SALES_ID SALES_DT
  ----------    ---------
      1    01-MAY-07


Note the new clause for (value), which allows you to directly reference partitions without explicitly calling them by their exact name. If you want to truncate or drop a partition, you can call this extended portioning syntax.


After the table is created in this manner, the PARTITIONING_TYPE column in view DBA_PART_TABLES shows INTERVAL

Tuesday, January 12, 2016

How to add a new disk in ASM

How to add a new disk in ASM Filesystem:-


/dev/sdi1            259610368    538324 259072044   1% /flash4
/etc/init.d/oracleasm createdisk ASMDISK02

====
/etc/init.d/oracleasm createdisk ASMDISK02 /dev/sdi1

=====
ALTER DISKGROUP DBGROUP1 ADD DISK 'ORCL:ASMDISK02';

alter system set asm_diskstring='/dev/oracleasm/disks/*' scope=both;

ALTER DISKGROUP DBGROUP1 ADD DISK 'ORCL:ASMDISK02' NAME  ASMDISK02  REBALANCE POWER 11;

ALTER DISKGROUP DBGROUP1 DROP DISK 'ORCL:ASMDISK02';

=============
select NAME, PATH, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, TOTAL_MB, FREE_MB
from v$asm_disk;


2. Add new ASM disks to the diskgroup:

alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1521';
alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1522';
alter diskgroup ORADB_DATA01_DG add disk 'ORCL:H_1253_1523';

alter diskgroup ORADB_DATA01_DG REBALANCE POWER 10; or
alter system set asm_power_limit=10 scope=memory;

-- Check the rebalance operation
select * from v$asm_operation;


Time taken to rebalance the diskgroup: app. 90 min.


3. Identify the disks to drop from the diskgroup:

set linesize 200;
col path format a20;

select NAME, PATH, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, TOTAL_MB, FREE_MB
from v$asm_disk;

ALTER DISKGROUP ORADB_DATA01_DG DROP DISK B_1530_1091;
ALTER DISKGROUP ORADB_DATA01_DG DROP DISK C_1530_1090;
ALTER DISKGROUP ORADB_DATA01_DG DROP DISK D_1530_1081;

alter diskgroup ORADB_DATA01_DG REBALANCE POWER 10; or
alter system set asm_power_limit=10 scope=memory;

-- Check the rebalance operation
select * from v$asm_operation;


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


/etc/init.d/oracleasm createdisk ORA_G1D6 /dev/sdn1

ALTER DISKGROUP DBGROUP1 ADD DISK 'ORCL:ORA_G1D5';
ALTER DISKGROUP DBGROUP1 ADD DISK 'ORCL:ORA_G1D6';
ALTER DISKGROUP DBGROUP1 ADD DISK 'ORCL:ORA_G1D7';
ALTER DISKGROUP DBGROUP1 ADD DISK 'ORCL:ORA_G1D8';

alter diskgroup DBGROUP1 REBALANCE POWER 10;

ALTER DISKGROUP DBGROUP1 DROP DISK 'ORCL:ASMDISK02';

ORA_G1D1
ORA_G1D2
ORA_G1D3
ORA_G1D4
ORA_G1D5
ORA_G1D6
ORA_G1D7
ORA_G1D8
 

How to estimate the size of a export dump:

How to estimate the size of a export dump:

expdp system/pw SCHEMAS=BACDBA LOGFILE=dir1:BACDBA.LOG estimate_only=y

How to change archive log destination:

How to change archive log destination:

ALTER SYSTEM SET log_archive_dest ='/flash2/arch/PMSDB/archivelog' scope=both;

or

ALTER SYSTEM ARCHIVE LOG ALL TO ‘/u04/oradata/PROD’;




How to change the database from noarchivelog to archivelog:

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  574619648 bytes
Fixed Size                   780240 bytes
Variable Size             304879664 bytes
Database Buffers          268435456 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ARCHIVE LOG START
Statement processed.
SQL> ALTER DATABASE OPEN;


Alternatively, add the above commands into your database's startup command script, and bounce the database.

The following parameters needs to be set for databases in ARCHIVELOG mode:

log_archive_start         = TRUE
log_archive_dest_1        = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1  = ENABLE
log_archive_format        = %d_%t_%s.arc

How to check block corruption:

How to check block corruption:


BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

select * from v$database_block_corruption;


How to configure redundancy:


CONFIGURE RETENTION POLICY TO REDUNDANCY 2;(Keep last two backups)

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;(Keep last three days backup)

How to copy SCHEMA STATISTICS:

How to copy SCHEMA STATISTICS:

SCHEMA NAME – TEST
Create the table to store the statistics-
EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’);
Export schema stats – will be stored in the ‘STATS_TABLE’
EXEC DBMS_STATS.export_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);
If required import these statistics back to TEST schema.
EXEC DBMS_STATS.import_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);
Finally drop the table created to backup the schema stats
EXEC DBMS_STATS.drop_stat_table(‘TEST’,'STATS_TABLE’);

How to kill a oracle session in windows

How to kill a oracle session in windows:

First select the spid:

SELECT p.SPID,s.Program,CLIENT_INFO,s.LOGON_TIME FROM V$SESSION s, V$PROCESS p WHERE s.PADDR=p.ADDR AND s.Program like 'RMAN%' ;

On Windows, there is a command-line utility called ORAKILL which lets you kill a specific thread in this situation. From a command prompt, run the following command:

orakill sid thread_id
that is
orakill orcl thread_id=spid from above command

Monday, January 11, 2016

DBMS_REDEFINITION was killed or stopped

How to resolve this issue even the DBMS_REDEFINITION was killed or stopped :
If we start the REDEFINITION with below

SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname      => 'TESTFLP',
orig_table => 'TAP_AUDIT_TRAIL',
int_table  => 'TAP_AUDIT_TRAIL_TMP');
END;
/   2    3    4    5    6    7

suddenly realised something is wrong or taking to long time.
If we kill the session next time when you start the REDEFINITION it will throw the below error
BEGIN
*
ERROR at line 1:
ORA-23539: table "TESTFLP"."TAP_AUDIT_TRAIL" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490
ORA-06512: at line 2

To remove the error first try with the below:

BEGIN
DBMS_REDEFINITION.abort_redef_table(
uname      => 'TESTFLP',
orig_table => 'TAP_AUDIT_TRAIL',
int_table  => 'TAP_AUDIT_TRAIL_TMP');
END;
/   2    3    4    5    6    7

=====
In case it is done on partition table

DBMS_REDEFINITION.ABORT_REDEF_TABLE (
   uname       IN VARCHAR2,
   orig_table  IN VARCHAR2,
   int_table   IN VARCHAR2,
  part_name    IN  VARCHAR2 := NULL);

 ABORT_REDEF_TABLE Procedure Parameters

Parameter    Description
uname

The schema name of the tables.

orig_table

The name of the table to be redefined.

int_table

The name of the interim table.

part_name

The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.

================
If nothing works then below

drop materialized view TESTFLP.TAP_AUDIT_TRAIL_TMP;
DROP SNAPSHOT LOG ON TESTFLP.TAP_AUDIT_TRAIL;
drop table TESTFLP.TAP_AUDIT_TRAIL_TMP;

Saturday, January 9, 2016

How to check which directories you have privilege to read & write before running export dump in oracle

To check on which directories you have privilege to read & write:
#################################################################

SQL> SELECT privilege, directory_name
2 FROM user_tab_privs t, all_directories d
3 WHERE t.table_name(+)=d.directory_name
4 ORDER BY 2,1;



How to enable debug mode in toad:

1. Grant debug any procedure to user_name;
2. Grant debug connect session to user_name;
3. GRANT EXECUTE ON DBMS_DEBUG to public;



========================
How to kill blocking session:
First check the session are not background;

set serveroutput on;
declare
CURSOR killist
IS
select s.sid, s.serial#
from v$session s
where last_call_et > 180
and s.status='ACTIVE'
and s.event<>'SQL*Net message from client'
and s.sid in (select HOLDING_SESSION from dba_blockers);
begin
for kills in killist loop
-- dbms_output.put_line('alter system kill session '''||kills.sid||','||kills.serial#||'''');
execute immediate 'alter system kill session ''' ||kills.sid||','||kills.serial#||'''';
end loop;
end;
/




##Killing a session


exec sys.kill(1147,13662)


Sunday, January 3, 2016

How to setup RMAN backup in Windows environment:-

How to setup RMAN backup in Windows OS environment:-

Command should be tested properly in Dev env and then should be used in production environment at user Risk:

Create a batch file say orcl_backup_rman.bat

==================In Batchfile=================
set logfile=%date%
set logfile=%logfile: =_%
set logfile=%logfile:/=_%
set logfile=orcl_backup_%logfile%.log

SET ORACLE_SID=ORCL

RMAN  nocatalog target / cmdfile 'U:\Oracle\orcl\bkp_scripts_logs\orcl_rman_backup.cmd' LOG='U:\Oracle\orcl\bkp_scripts_logs\logs\%logfile%'


Create a orcl_rman_backup.cmd file and put this command:

BACKUP AS COMPRESSED BACKUPSET database format 'U:\Oracle\orcl\backup\%U' plus archivelog format 'U:\Oracle\orcl\backup\%U' delete input;


delete force noprompt obsolete;

Status Code Definitions for my own Reference Others can use depending on your working Knowledge and understanding.

Status Code Definitions for my own Reference while others can use depending on your working Knowledge and understanding.

Others can read but use case and definition should vary and may not consider it as the final reference.

Finally others can use solely at there own Risks:


Status Code Definitions:-

Each Status-Code is described below, including a description of which method(s) it can follow and any metainformation required in the response.

10.1 Informational 1xx

This class of status code indicates a provisional response, consisting only of the Status-Line and optional headers, and is terminated by an empty line. There are no required headers for this class of status code. Since HTTP/1.0 did not define any 1xx status codes, servers MUST NOT send a 1xx response to an HTTP/1.0 client except under experimental conditions.

A client MUST be prepared to accept one or more 1xx status responses prior to a regular response, even if the client does not expect a 100 (Continue) status message. Unexpected 1xx status responses MAY be ignored by a user agent.

Proxies MUST forward 1xx responses, unless the connection between the proxy and its client has been closed, or unless the proxy itself requested the generation of the 1xx response. (For example, if a

proxy adds a "Expect: 100-continue" field when it forwards a request, then it need not forward the corresponding 100 (Continue) response(s).)

10.1.1 100 Continue

The client SHOULD continue with its request. This interim response is used to inform the client that the initial part of the request has been received and has not yet been rejected by the server. The client SHOULD continue by sending the remainder of the request or, if the request has already been completed, ignore this response. The server MUST send a final response after the request has been completed. See section 8.2.3 for detailed discussion of the use and handling of this status code.

10.1.2 101 Switching Protocols

The server understands and is willing to comply with the client's request, via the Upgrade message header field (section 14.42), for a change in the application protocol being used on this connection. The server will switch protocols to those defined by the response's Upgrade header field immediately after the empty line which terminates the 101 response.

The protocol SHOULD be switched only when it is advantageous to do so. For example, switching to a newer version of HTTP is advantageous over older versions, and switching to a real-time, synchronous protocol might be advantageous when delivering resources that use such features.

10.2 Successful 2xx

This class of status code indicates that the client's request was successfully received, understood, and accepted.

10.2.1 200 OK

The request has succeeded. The information returned with the response is dependent on the method used in the request, for example:

GET an entity corresponding to the requested resource is sent in the response;

HEAD the entity-header fields corresponding to the requested resource are sent in the response without any message-body;

POST an entity describing or containing the result of the action;

TRACE an entity containing the request message as received by the end server.

10.2.2 201 Created

The request has been fulfilled and resulted in a new resource being created. The newly created resource can be referenced by the URI(s) returned in the entity of the response, with the most specific URI for the resource given by a Location header field. The response SHOULD include an entity containing a list of resource characteristics and location(s) from which the user or user agent can choose the one most appropriate. The entity format is specified by the media type given in the Content-Type header field. The origin server MUST create the resource before returning the 201 status code. If the action cannot be carried out immediately, the server SHOULD respond with 202 (Accepted) response instead.

A 201 response MAY contain an ETag response header field indicating the current value of the entity tag for the requested variant just created, see section 14.19.

10.2.3 202 Accepted

The request has been accepted for processing, but the processing has not been completed. The request might or might not eventually be acted upon, as it might be disallowed when processing actually takes place. There is no facility for re-sending a status code from an asynchronous operation such as this.

The 202 response is intentionally non-committal. Its purpose is to allow a server to accept a request for some other process (perhaps a batch-oriented process that is only run once per day) without requiring that the user agent's connection to the server persist until the process is completed. The entity returned with this response SHOULD include an indication of the request's current status and either a pointer to a status monitor or some estimate of when the user can expect the request to be fulfilled.

10.2.4 203 Non-Authoritative Information

The returned metainformation in the entity-header is not the definitive set as available from the origin server, but is gathered from a local or a third-party copy. The set presented MAY be a subset or superset of the original version. For example, including local annotation information about the resource might result in a superset of the metainformation known by the origin server. Use of this response code is not required and is only appropriate when the response would otherwise be 200 (OK).

10.2.5 204 No Content

The server has fulfilled the request but does not need to return an entity-body, and might want to return updated metainformation. The response MAY include new or updated metainformation in the form of entity-headers, which if present SHOULD be associated with the requested variant.

If the client is a user agent, it SHOULD NOT change its document view from that which caused the request to be sent. This response is primarily intended to allow input for actions to take place without causing a change to the user agent's active document view, although any new or updated metainformation SHOULD be applied to the document currently in the user agent's active view.

The 204 response MUST NOT include a message-body, and thus is always terminated by the first empty line after the header fields.

10.2.6 205 Reset Content

The server has fulfilled the request and the user agent SHOULD reset the document view which caused the request to be sent. This response is primarily intended to allow input for actions to take place via user input, followed by a clearing of the form in which the input is given so that the user can easily initiate another input action. The response MUST NOT include an entity.

10.2.7 206 Partial Content

The server has fulfilled the partial GET request for the resource. The request MUST have included a Range header field (section 14.35) indicating the desired range, and MAY have included an If-Range header field (section 14.27) to make the request conditional.

The response MUST include the following header fields:

      - Either a Content-Range header field (section 14.16) indicating
        the range included with this response, or a multipart/byteranges
        Content-Type including Content-Range fields for each part. If a
        Content-Length header field is present in the response, its
        value MUST match the actual number of OCTETs transmitted in the
        message-body.
      - Date
      - ETag and/or Content-Location, if the header would have been sent
        in a 200 response to the same request
      - Expires, Cache-Control, and/or Vary, if the field-value might
        differ from that sent in any previous response for the same
        variant
If the 206 response is the result of an If-Range request that used a strong cache validator (see section 13.3.3), the response SHOULD NOT include other entity-headers. If the response is the result of an If-Range request that used a weak validator, the response MUST NOT include other entity-headers; this prevents inconsistencies between cached entity-bodies and updated headers. Otherwise, the response MUST include all of the entity-headers that would have been returned with a 200 (OK) response to the same request.

A cache MUST NOT combine a 206 response with other previously cached content if the ETag or Last-Modified headers do not match exactly, see 13.5.4.

A cache that does not support the Range and Content-Range headers MUST NOT cache 206 (Partial) responses.

10.3 Redirection 3xx

This class of status code indicates that further action needs to be taken by the user agent in order to fulfill the request. The action required MAY be carried out by the user agent without interaction with the user if and only if the method used in the second request is GET or HEAD. A client SHOULD detect infinite redirection loops, since such loops generate network traffic for each redirection.

      Note: previous versions of this specification recommended a
      maximum of five redirections. Content developers should be aware
      that there might be clients that implement such a fixed
      limitation.
10.3.1 300 Multiple Choices

The requested resource corresponds to any one of a set of representations, each with its own specific location, and agent- driven negotiation information (section 12) is being provided so that the user (or user agent) can select a preferred representation and redirect its request to that location.

Unless it was a HEAD request, the response SHOULD include an entity containing a list of resource characteristics and location(s) from which the user or user agent can choose the one most appropriate. The entity format is specified by the media type given in the Content- Type header field. Depending upon the format and the capabilities of

the user agent, selection of the most appropriate choice MAY be performed automatically. However, this specification does not define any standard for such automatic selection.

If the server has a preferred choice of representation, it SHOULD include the specific URI for that representation in the Location field; user agents MAY use the Location field value for automatic redirection. This response is cacheable unless indicated otherwise.

10.3.2 301 Moved Permanently

The requested resource has been assigned a new permanent URI and any future references to this resource SHOULD use one of the returned URIs. Clients with link editing capabilities ought to automatically re-link references to the Request-URI to one or more of the new references returned by the server, where possible. This response is cacheable unless indicated otherwise.

The new permanent URI SHOULD be given by the Location field in the response. Unless the request method was HEAD, the entity of the response SHOULD contain a short hypertext note with a hyperlink to the new URI(s).

If the 301 status code is received in response to a request other than GET or HEAD, the user agent MUST NOT automatically redirect the request unless it can be confirmed by the user, since this might change the conditions under which the request was issued.

      Note: When automatically redirecting a POST request after
      receiving a 301 status code, some existing HTTP/1.0 user agents
      will erroneously change it into a GET request.
10.3.3 302 Found

The requested resource resides temporarily under a different URI. Since the redirection might be altered on occasion, the client SHOULD continue to use the Request-URI for future requests. This response is only cacheable if indicated by a Cache-Control or Expires header field.

The temporary URI SHOULD be given by the Location field in the response. Unless the request method was HEAD, the entity of the response SHOULD contain a short hypertext note with a hyperlink to the new URI(s).

If the 302 status code is received in response to a request other than GET or HEAD, the user agent MUST NOT automatically redirect the request unless it can be confirmed by the user, since this might change the conditions under which the request was issued.

      Note: RFC 1945 and RFC 2068 specify that the client is not allowed
      to change the method on the redirected request.  However, most
      existing user agent implementations treat 302 as if it were a 303
      response, performing a GET on the Location field-value regardless
      of the original request method. The status codes 303 and 307 have
      been added for servers that wish to make unambiguously clear which
      kind of reaction is expected of the client.
10.3.4 303 See Other

The response to the request can be found under a different URI and SHOULD be retrieved using a GET method on that resource. This method exists primarily to allow the output of a POST-activated script to redirect the user agent to a selected resource. The new URI is not a substitute reference for the originally requested resource. The 303 response MUST NOT be cached, but the response to the second (redirected) request might be cacheable.

The different URI SHOULD be given by the Location field in the response. Unless the request method was HEAD, the entity of the response SHOULD contain a short hypertext note with a hyperlink to the new URI(s).

      Note: Many pre-HTTP/1.1 user agents do not understand the 303
      status. When interoperability with such clients is a concern, the
      302 status code may be used instead, since most user agents react
      to a 302 response as described here for 303.
10.3.5 304 Not Modified

If the client has performed a conditional GET request and access is allowed, but the document has not been modified, the server SHOULD respond with this status code. The 304 response MUST NOT contain a message-body, and thus is always terminated by the first empty line after the header fields.

The response MUST include the following header fields:

      - Date, unless its omission is required by section 14.18.1
If a clockless origin server obeys these rules, and proxies and clients add their own Date to any response received without one (as already specified by [RFC 2068], section 14.19), caches will operate correctly.

      - ETag and/or Content-Location, if the header would have been sent
        in a 200 response to the same request
      - Expires, Cache-Control, and/or Vary, if the field-value might
        differ from that sent in any previous response for the same
        variant
If the conditional GET used a strong cache validator (see section 13.3.3), the response SHOULD NOT include other entity-headers. Otherwise (i.e., the conditional GET used a weak validator), the response MUST NOT include other entity-headers; this prevents inconsistencies between cached entity-bodies and updated headers.

If a 304 response indicates an entity not currently cached, then the cache MUST disregard the response and repeat the request without the conditional.

If a cache uses a received 304 response to update a cache entry, the cache MUST update the entry to reflect any new field values given in the response.

10.3.6 305 Use Proxy

The requested resource MUST be accessed through the proxy given by the Location field. The Location field gives the URI of the proxy. The recipient is expected to repeat this single request via the proxy. 305 responses MUST only be generated by origin servers.

      Note: RFC 2068 was not clear that 305 was intended to redirect a
      single request, and to be generated by origin servers only.  Not
      observing these limitations has significant security consequences.
10.3.7 306 (Unused)

The 306 status code was used in a previous version of the specification, is no longer used, and the code is reserved.

10.3.8 307 Temporary Redirect

The requested resource resides temporarily under a different URI. Since the redirection MAY be altered on occasion, the client SHOULD continue to use the Request-URI for future requests. This response is only cacheable if indicated by a Cache-Control or Expires header field.

The temporary URI SHOULD be given by the Location field in the response. Unless the request method was HEAD, the entity of the response SHOULD contain a short hypertext note with a hyperlink to the new URI(s) , since many pre-HTTP/1.1 user agents do not understand the 307 status. Therefore, the note SHOULD contain the information necessary for a user to repeat the original request on the new URI.

If the 307 status code is received in response to a request other than GET or HEAD, the user agent MUST NOT automatically redirect the request unless it can be confirmed by the user, since this might change the conditions under which the request was issued.

10.4 Client Error 4xx

The 4xx class of status code is intended for cases in which the client seems to have erred. Except when responding to a HEAD request, the server SHOULD include an entity containing an explanation of the error situation, and whether it is a temporary or permanent condition. These status codes are applicable to any request method. User agents SHOULD display any included entity to the user.

If the client is sending data, a server implementation using TCP SHOULD be careful to ensure that the client acknowledges receipt of the packet(s) containing the response, before the server closes the input connection. If the client continues sending data to the server after the close, the server's TCP stack will send a reset packet to the client, which may erase the client's unacknowledged input buffers before they can be read and interpreted by the HTTP application.

10.4.1 400 Bad Request

The request could not be understood by the server due to malformed syntax. The client SHOULD NOT repeat the request without modifications.

10.4.2 401 Unauthorized

The request requires user authentication. The response MUST include a WWW-Authenticate header field (section 14.47) containing a challenge applicable to the requested resource. The client MAY repeat the request with a suitable Authorization header field (section 14.8). If the request already included Authorization credentials, then the 401 response indicates that authorization has been refused for those credentials. If the 401 response contains the same challenge as the prior response, and the user agent has already attempted authentication at least once, then the user SHOULD be presented the entity that was given in the response, since that entity might include relevant diagnostic information. HTTP access authentication is explained in "HTTP Authentication: Basic and Digest Access Authentication" [43].

10.4.3 402 Payment Required

This code is reserved for future use.

10.4.4 403 Forbidden

The server understood the request, but is refusing to fulfill it. Authorization will not help and the request SHOULD NOT be repeated. If the request method was not HEAD and the server wishes to make public why the request has not been fulfilled, it SHOULD describe the reason for the refusal in the entity. If the server does not wish to make this information available to the client, the status code 404 (Not Found) can be used instead.

10.4.5 404 Not Found

The server has not found anything matching the Request-URI. No indication is given of whether the condition is temporary or permanent. The 410 (Gone) status code SHOULD be used if the server knows, through some internally configurable mechanism, that an old resource is permanently unavailable and has no forwarding address. This status code is commonly used when the server does not wish to reveal exactly why the request has been refused, or when no other response is applicable.

10.4.6 405 Method Not Allowed

The method specified in the Request-Line is not allowed for the resource identified by the Request-URI. The response MUST include an Allow header containing a list of valid methods for the requested resource.

10.4.7 406 Not Acceptable

The resource identified by the request is only capable of generating response entities which have content characteristics not acceptable according to the accept headers sent in the request.

Unless it was a HEAD request, the response SHOULD include an entity containing a list of available entity characteristics and location(s) from which the user or user agent can choose the one most appropriate. The entity format is specified by the media type given in the Content-Type header field. Depending upon the format and the capabilities of the user agent, selection of the most appropriate choice MAY be performed automatically. However, this specification does not define any standard for such automatic selection.

      Note: HTTP/1.1 servers are allowed to return responses which are
      not acceptable according to the accept headers sent in the
      request. In some cases, this may even be preferable to sending a
      406 response. User agents are encouraged to inspect the headers of
      an incoming response to determine if it is acceptable.
If the response could be unacceptable, a user agent SHOULD temporarily stop receipt of more data and query the user for a decision on further actions.

10.4.8 407 Proxy Authentication Required

This code is similar to 401 (Unauthorized), but indicates that the client must first authenticate itself with the proxy. The proxy MUST return a Proxy-Authenticate header field (section 14.33) containing a challenge applicable to the proxy for the requested resource. The client MAY repeat the request with a suitable Proxy-Authorization header field (section 14.34). HTTP access authentication is explained in "HTTP Authentication: Basic and Digest Access Authentication" [43].

10.4.9 408 Request Timeout

The client did not produce a request within the time that the server was prepared to wait. The client MAY repeat the request without modifications at any later time.

10.4.10 409 Conflict

The request could not be completed due to a conflict with the current state of the resource. This code is only allowed in situations where it is expected that the user might be able to resolve the conflict and resubmit the request. The response body SHOULD include enough

information for the user to recognize the source of the conflict. Ideally, the response entity would include enough information for the user or user agent to fix the problem; however, that might not be possible and is not required.

Conflicts are most likely to occur in response to a PUT request. For example, if versioning were being used and the entity being PUT included changes to a resource which conflict with those made by an earlier (third-party) request, the server might use the 409 response to indicate that it can't complete the request. In this case, the response entity would likely contain a list of the differences between the two versions in a format defined by the response Content-Type.

10.4.11 410 Gone

The requested resource is no longer available at the server and no forwarding address is known. This condition is expected to be considered permanent. Clients with link editing capabilities SHOULD delete references to the Request-URI after user approval. If the server does not know, or has no facility to determine, whether or not the condition is permanent, the status code 404 (Not Found) SHOULD be used instead. This response is cacheable unless indicated otherwise.

The 410 response is primarily intended to assist the task of web maintenance by notifying the recipient that the resource is intentionally unavailable and that the server owners desire that remote links to that resource be removed. Such an event is common for limited-time, promotional services and for resources belonging to individuals no longer working at the server's site. It is not necessary to mark all permanently unavailable resources as "gone" or to keep the mark for any length of time -- that is left to the discretion of the server owner.

10.4.12 411 Length Required

The server refuses to accept the request without a defined Content- Length. The client MAY repeat the request if it adds a valid Content-Length header field containing the length of the message-body in the request message.

10.4.13 412 Precondition Failed

The precondition given in one or more of the request-header fields evaluated to false when it was tested on the server. This response code allows the client to place preconditions on the current resource metainformation (header field data) and thus prevent the requested method from being applied to a resource other than the one intended.

10.4.14 413 Request Entity Too Large

The server is refusing to process a request because the request entity is larger than the server is willing or able to process. The server MAY close the connection to prevent the client from continuing the request.

If the condition is temporary, the server SHOULD include a Retry- After header field to indicate that it is temporary and after what time the client MAY try again.

10.4.15 414 Request-URI Too Long

The server is refusing to service the request because the Request-URI is longer than the server is willing to interpret. This rare condition is only likely to occur when a client has improperly converted a POST request to a GET request with long query information, when the client has descended into a URI "black hole" of redirection (e.g., a redirected URI prefix that points to a suffix of itself), or when the server is under attack by a client attempting to exploit security holes present in some servers using fixed-length buffers for reading or manipulating the Request-URI.

10.4.16 415 Unsupported Media Type

The server is refusing to service the request because the entity of the request is in a format not supported by the requested resource for the requested method.

10.4.17 416 Requested Range Not Satisfiable

A server SHOULD return a response with this status code if a request included a Range request-header field (section 14.35), and none of the range-specifier values in this field overlap the current extent of the selected resource, and the request did not include an If-Range request-header field. (For byte-ranges, this means that the first- byte-pos of all of the byte-range-spec values were greater than the current length of the selected resource.)

When this status code is returned for a byte-range request, the response SHOULD include a Content-Range entity-header field specifying the current length of the selected resource (see section 14.16). This response MUST NOT use the multipart/byteranges content- type.

10.4.18 417 Expectation Failed

The expectation given in an Expect request-header field (see section 14.20) could not be met by this server, or, if the server is a proxy, the server has unambiguous evidence that the request could not be met by the next-hop server.

10.5 Server Error 5xx

Response status codes beginning with the digit "5" indicate cases in which the server is aware that it has erred or is incapable of performing the request. Except when responding to a HEAD request, the server SHOULD include an entity containing an explanation of the error situation, and whether it is a temporary or permanent condition. User agents SHOULD display any included entity to the user. These response codes are applicable to any request method.

10.5.1 500 Internal Server Error

The server encountered an unexpected condition which prevented it from fulfilling the request.

10.5.2 501 Not Implemented

The server does not support the functionality required to fulfill the request. This is the appropriate response when the server does not recognize the request method and is not capable of supporting it for any resource.

10.5.3 502 Bad Gateway

The server, while acting as a gateway or proxy, received an invalid response from the upstream server it accessed in attempting to fulfill the request.

10.5.4 503 Service Unavailable

The server is currently unable to handle the request due to a temporary overloading or maintenance of the server. The implication is that this is a temporary condition which will be alleviated after some delay. If known, the length of the delay MAY be indicated in a Retry-After header. If no Retry-After is given, the client SHOULD handle the response as it would for a 500 response.

      Note: The existence of the 503 status code does not imply that a
      server must use it when becoming overloaded. Some servers may wish
      to simply refuse the connection.
10.5.5 504 Gateway Timeout

The server, while acting as a gateway or proxy, did not receive a timely response from the upstream server specified by the URI (e.g. HTTP, FTP, LDAP) or some other auxiliary server (e.g. DNS) it needed to access in attempting to complete the request.

      Note: Note to implementors: some deployed proxies are known to
      return 400 or 500 when DNS lookups time out.
10.5.6 505 HTTP Version Not Supported

The server does not support, or refuses to support, the HTTP protocol version that was used in the request message. The server is indicating that it is unable or unwilling to complete the request using the same major version as the client, as described in section 3.1, other than with this error message. The response SHOULD contain an entity describing why that version is not supported and what other protocols are supported by that server.