Posts

Oracle 12c Common and Local users Creation

                Oracle 12c Common and Local users Creation Error : ORA-65096 ORA-65049 In 12c when you login to a CDB database you default to the CDB$ROOT container. [oracle@rac1 db_1]$ echo $ORACLE_SID T12 [oracle@rac1 db_1]$ ps -ef | grep pmon oracle   21182     1  0 13:21 ?        00:00:00 ora_pmon_T12 [oracle@rac1 db_1]$ sqlplus / as sysdba SQL> show con_name CON_NAME ----------- CDB$ROOT An attempt to create users in the CDB$ROOT container may result in ORA-65096 or ORA-65049 . SQL> create user test identified by test; create user test identified by test             * ERROR at line 1: ORA-65096: invalid common user or role name oerr ora 65096 65096, 00000, "invalid common user or role name" // *Cause:  An attempt was made to create a common user or role with a name //          that wass not valid for comm...
Post a Comment
Read more

Invisible Indexes in Oracle Database 11g Release 1

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...
Post a Comment
Read more

How to use set newname option in rman restore

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;...
Post a Comment
Read more

how to uninstall oracle 11gR2 ---> oracle introduced new concept Deinstall utility

Image
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 ]           ...
Post a Comment
Read more

PASSWORD not available from oracle 11g in DBA_USERS view

Image
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...
Post a Comment
Read more

XML db installation steps

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...
Post a Comment
Read more

Purging trace and dump files with 11g ADRCI

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/...
Post a Comment
Read more