ANIL VEJENDLA

Invisible Indexes in Oracle Database 11g Release 1 Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the  OPTIMIZER_USE_INVISIBLE_INDEXES  parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the  INVISIBLE keyword, and their visibility can be toggled using the  ALTER INDEX  command. CREATE INDEX index_name ON table_name(column_name) INVISIBLE; ALTER INDEX index_name INVISIBLE; ALTER INDEX index_name VISIBLE; The following script creates and populates a table, then creates an invisible index on it. CREATE TABLE ii_tab (   id  NUMBER ); BEGIN   FOR i IN 1 .. 10000 LOOP     INSERT INTO ii_tab VALUES (i);   END LOOP;   COMMIT; END; / CREATE INDEX ii_tab_id ON ii_tab(id) INVISIBLE; EXEC DBMS_STATS.gather_table_stats(USER, 'ii_tab', cascade=> TRUE); A query using the i...

I was trying to restore  rman full backup and it was failed due to one of the required directory  is not available in  place . I have total 693 data files and the last data file 693 is trying to restore in /u031 mount point which is not available in my destination server. Using SET NEWNAME   we can change the data file location before restore. REACTIVE Case 1 ::   If you want to restore only single data file after completion of restore and failed for only  one data file. RUN {     set until sequence = 88903;        Send 'NB_ORA_CLIENT=xxxxxx.ffdc.sbc.com';         SET NEWNAME FOR DATAFILE  ' /u031 /oradata/qprd/xxxxxx.dbf' to ' /u030 /oradata/qprd/xxxxxx.dbf';     RESTORE DATAFILE 693;     SWITCH DATAFILE 693;          ( it will update the control file )     RECOVER DATAFILE 693;...

Oracle deinstall utility was a new feature which was added as part of 11gr2 release. Now one cannot uninstall oracle home using $ORACLE_HOME/oui/bin/runInstaller GUI. We need to invoke a CLI deinstall present in $ORACLE_HOME/deinstall. This CLI ask few questions related to configuration and then will start DEINSTALL. This deinstall utility is also available as a separate download from the OTN site.  We can download this file (eg: aix.ppc64_11gR2_deinstall.zip) & unzip it. It will create following directory structure Check the usage $ deinstall Location of logs /home/ora11gr2/logs/ ############ ORACLE DEINSTALL & DECONFIG TOOL START ############ Usage: deinstall -home <Complete path of Oracle home>                 [ -silent ]                 [ -checkonly ]           ...

ALTER USER IDENTIFIED BY VALUES with Oracle 11g Oracle 11g introduces case-sensitive passwords for databases created with the default Oracle Database 11g enhanced security. With the new initialization parameter SEC_CASE_SENTITIVE_LOGON, it’s possible to enable or disable password case sensitivity in the database. The default value of this parameter is TRUE: SQL> SELECT value FROM v$parameter WHERE name = ‘sec_case_sensitive_logon’; VALUE ————— TRUE When we issue a CREATE/ALTER USER IDENTIFIED BY PASSWORD command, both the insensitive and the sensitive password hashes are saved (but the user’s password is sensitive). The two hashes are stored in the SYS.USER$ view (not into the DBA_USERS view): SQL> CREATE USER anilve IDENTIFIED BY anilve; User created. SQL> GRANT connect, resource TO anilve; Grant succeeded. SQL> CONNECT anilve/Anilve ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> CONNECT an...

XMLDB has been installed in <DB_NAME> on <Server> EX: If the database name QDAPUB NOTE:-  needs to restart listener for QDAPUB db The steps are as below:- 1) Check java is installed or not stpes for JVM installation:- select comp_name,version,status from dba_registry; If the above doesn't list "JServer JAVA Virtual Machine 9.2.0.6.0" or similar, install Oracle Java Virtual Machine:     shutdown immediate     startup mount     alter system set "_system_trig_enabled" = false scope=memory;     alter database open;     @?/javavm/install/initjvm.sql     @?/xdk/admin/initxml.sql     @?/xdk/admin/xmlja.sql     @?/rdbms/admin/catjava.sql     shutdown immediate     startup 2)  XMLDB  INSTALL STEPS: --   Create XDB tablespace 1) Connect as sysdba and run the catqm.sql script. Maintain a spool of the sc...

Purging trace and dump files with 11g ADRCI In previous versions of Oracle prior to 11g, we had to use our own housekeeping scripts to purge the udump, cdump and bdump directories. In Oracle 11g, we now have the ADR (Automatic Diagnostic Repository) which is defined by the diagnostic_dest parameter. So how are unwanted trace and core dump files cleaned out in 11g automatically? This is done by the MMON background process. There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data. LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings. SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files The ADRCI command show control will show us what the current purge settings are as shown below. adrci> show control ADR Home = /u01/app/oracle/diag/...

HI All, Now it is very easy to read alertSID.log file using new Tool "ADRCI".  oracle@fltv0004[/home/oracle] adrci ADRCI: Release 11.2.0.2.0 - Production on Wed May 4 04:12:51 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. ADR base = "/oracle/app/oracle" From adrci command line we need to fire show alert command to read alertlog. adrci> show alert Choose the alert log from the following homes to view: 1: diag/asmtool/user_oracle/host_2171877434_80 2: diag/tnslsnr/fltv0004/listener 3: diag/clients/user_oracle/host_2171877434_80 4: diag/rdbms/ddss/ddss 5: diag/rdbms/qimg/qimg 6: diag/rdbms/tamc/tamc 7: diag/rdbms/cdss/cdss 8: diag/rdbms/tdss/tdss 9: diag/rdbms/trmd/trmd 10: diag/asm/+asm/+ASM Q: to quit Please select option: 7   (Now it will open the cdss alert log file  ) Just two commands magic 1.Adrci 2.Show alert Select which db alert you need  from given options

Upgrading ASM manually to version 11.2.0.2 Due to a bug in Oracle upgrade process it is impossible to upgrade ASM 10g to ASM 11g using the GUI. The proposed method uninstalls ASM 10g and installs ASM 11g from scratch. 1.       Ask a UNIX admin to create a new disk called “dummy” of any size at the same location as the rest of the existing Oracle data disks. It will be used during the installation. 2.       Download  the following file containing ASM 11.2.0.2: p10098816_112020_Linux-x86-64_3of7.zip 3.       Shutdown ALL databases, listener and ASM instance on the target box. 4.       Save the old /usr/local/bin/dbhome and /usr/local/bin/oraenv to some backup location just in case we’ll need them later on. 5.       As a root, go to the old $ORACLE_HOME and run the following command in order to unregister ASM 10g and stop all ASM-related processes : localc...