sec_case_sensitive_logon – > This initialisation(init) parameter enables or disabled password case sensitivity in the database. when sec_case_sensitive_logon is TRUE – -> t he database use login passwords are case sensitive. when sec_case_sensitive_logon is FALSE – -> means database use login passwords are case insensitive. DEFAULT VALUE IS TRUE . Lets see […]

Multiplexing in control_file is one of the best practices of oracle database setup. It means keeping control files in different mount point or disk groups , so that in case one mount points is inaccessible, control file can be accessed from the available mount point. NOTE – > This activity needs downtime.       […]

This article contains all the useful gather statistics related commands. 1. Gather dictionary stats: — It gathers statistics for dictionary schemas ‘SYS’, ‘SYSTEM’ and other internal schemas. EXEC DBMS_STATS.gather_dictionary_stats; 2. Gather fixed object stats: — Fixed object means gv$ or v$views EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 3. Gather full database stats: EXEC DBMS_STATS.gather_database_stats; — With estimate_percent to 15 […]

If your present archivelog mountpoint is FULL or for any other reason, you want to change the archivelog destination in the database, then follow the below steps. NOTE – > It can be done ,when the database up are running. No downtime required.  FOR STANDALONE DATABASE: 1. Find the current archivelog location: SQL> archive log […]

Invisible index is introduced in oracle 11g. As the name suggest, this type of index will be ignored by database optimizer, as if it doesn’t exists. EXAMPLE: —  CREATE AN INDEX ( BY DEFAULT THE INDEX WILL BE A VISIBLE ONE) SQL> desc emp Name Null? Type —————————————– ——– —————————- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME […]

The job of MMON( Manageability Monitor) background process is to perform tasks like taking AWR snapshots and automatic diagnostic analysis. Sometime, MMON process crashes or get suspended . Follow below steps to restart the MMON process: 1. Grant restricted session to public: This grant is required to avoid any impact on the sessions connecting to […]

Below are steps to move or rename controlfile to a new location(Mount point or diskgroup). In ORACLE RAC:(Move from +ORACLDG diskgroup to +DATA diskgroup) 1. Get the current control_file location SQL> show parameter control_files NAME TYPE VALUE ———————————— ——————————– —————————— control_files string +ORACLDG/DBACLASS/CONTROLFILE/control01.ctl 2. Set the new location of controlfile: SQL> alter system set control_files=’+DATA’ […]

Like windows have recyclebin, Oracle database has also provided recyclebin, which keep all the dropped objects. When we drop a table(DROP TABLE TABLE_NAME) in the database, the tables will logically be removed, but it still exists in the same tablespace but with a prefix BIN$$. And it will not release the space also. NOTE – The […]

Though GUI is a preferable method to do client installation, But sometimes DBAs might be not to be able to xmanager to enable GUI for the server. In that case, installation using response file will be useful. In this example, we will do the installation of Oracle client 12.1.0.2 version. 1. Download Oracle client software […]

Below are the steps for applying quarterly PSU patch on Oracle 11g database. 1. Download the patch from Oracle support portal.   2. Validate patch version: opatch utility is used to apply patches to the database. So before applying patch, we need to check whether the existing opatch version is supported for the patch or […]