DATABASE

How to create database using dbca in silent mode – 19C

From oracle 19c onward, we can create a database using dbca in silent mode with help of response file. default response file location: export ORACLE_HOME=/oracle/app/oracle/product/19.9.0.0/dbhome_1 cd $ORACLE_HOME/assistants/dbca By using the default response files, you can create a new response file, as per your requirement. Below is the response file i have created. cat /export/home/oracle/db_create.rsp responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0 […]

How to apply JDK patch in oracle database

Recently we got a notification from security Team, that the java versions inside the ORACLE_HOME were outdated, and those need to be updated to the latest one. So We checked in  oracle support portal and found out what is the latest jdk patch available and then applied the same on respective servers. In this below […]

Useful OPATCH commands

1. list inventory details of patch. $ORACLE_HOME/OPatch/opatch lsinventory 2. list patchsets applied : $ORACLE_HOME/OPatch/opatch lspatches 3. Find opatch version: $ORACLE_HOME/OPatch/opatch version 4. Find details of a particular patch(before applying): $ORACLE_HOME/OPatch/opatch query -all {PATCH_PATH} $ORACLE_HOME/OPatch/opatch query -all /software/PSUPATCH/30089984 5. Apply a patch to RDBMS HOME: — You may need to shutdown the database and listener services: […]

sec_case_sensitive_logon parameter in oracle

         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 […]

How to multiplex control file in standalone database

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.       […]

Useful gather statistics commands in oracle

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 […]

how to change archivelog destination in oracle

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 in oracle database

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 […]

Restart MMON process in oracle database

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 […]

How to move controlfile to a new location in oracle

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’ […]