Learn Database Concepts

 Problem: [oracle@linux5 database]$ . runInstaller bash: /bin/install/.oui: No such file or directory [oracle@linux5 database]$ uname -a Linux linux5 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013 x86_64 x86_64 x86_64 GNU/Linux Solution: [oracle@linux5 database]$ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB.   Actual 20461 MB    Passed Checking swap space: must be greater than 150 MB.   Actual 4031 MB    Passed Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-11-22_09-46-02AM. Please wait ...[oracle@linux5 database]$

Usage : we can use dbshut script file in $ORACLE_HOME/bin to shutdown  database & listener.  [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_ orcl oracle   21133 19211  0 11:01 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$ dbshut Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/shutdown.log [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   21287 19211  0 11:09 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$ Error : Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr [oracle@Linux03 bin]$ dbshut Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr  Solution (same as above): edit dbshut script and change From : ORACLE_HOME_LISTNER=$1   To : ORACLE_HOME_LISTNER=$ORACLE_HOME Note :   One pre-req for this script...

Usage : we can use dbstart script file in $ORACLE_HOME/bin to start database & listener. [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   20588 19211  0 10:56 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$ dbstart Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/startup.log [oracle@Linux03 bin]$ ps -ef|grep pmon oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl oracle   21035 19211  0 10:57 pts/0    00:00:00 grep pmon [oracle@Linux03 bin]$  Common error with dbstart script : Error : /u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found [oracle@Linux03 bin]$ dbstart /u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found Solution : Edit dbstart script and change (~ line 275) From : ORACLE_HOME_LISTNER=$1 To...

I have a table AUDIT_LOGONS, it has 5 partitions in it and one partition is defined as MAXVALUE. All partitions has some data (see below screen) in it except the MAXVALUE partition. Now I want to add a new partition which has date values less than 2016-05-31  But I am getting error ORA-14074 sql : alter table AUDIT_LOGONS add partition AUDIT_LOGONS_P1 VALUES LESS THAN (TO_DATE(' 2016-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')); and I get this error : SQL Error: ORA-14074 : partition bound must collate higher than that of the last partition 14074. 00000 -  " partition bound must collate higher than that of the last partition " *Cause:    Partition bound specified in ALTER TABLE ADD PARTITION Solution 1: We can add a sub-partition to the partition that was set with MAXVALUE (AUDIT_LOGONS5 in this case). In below sql we are modifying the partition audit_logons5 adding a sub-parition audit_logons6 which will have all the data which has date below "2016-09-30...

 TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking: unable to parse configuration parameters I  Received following errors when trying to startup my listener. I verified the listener.ora and sqlnet.ora files and everything seemed to look normal.   [oracle@Linux03 encryption_keystore]$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:05:32 Copyright (c) 1991, 2014, Oracle.  All rights reserved. Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521))) TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking...

ORA-00020: maximum number of processes This error occurs when your total numbers of sessions connecting to oracle database has exceeded the max limit set in parameter file. Simplest way to overcome this error is to reset the max sessions value to a greater value than existing.Here is how to do it   oracle@LINUX201:[~] $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:20:26 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-00020: maximum number of processes (500) exceeded oracle@LINUX201:[~] $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:23:42 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR: ORA-00020: maximum number of processes (500) exceeded Enter user-name: Disconnected from ORACLE I wasn't able get into the oracle database to kill some database session. So I tried to kill few sessions on OS to make my way into DB. oracle@LINUX201:[~] $ ps -ef|grep oracle . . . . . . ....

Inserting Data with DML Error Logging: When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature. To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time. DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements. -------------------------------------------------------- --  DDL for Table ATEST1 ---------------------...

In this scenario I am trying to increase the value of parameter memory_max_target.  My initial  memory_max_target = 804 I want to increase it to 900 SQL> show parameter sga NAME     TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga     boolean FALSE pre_page_sga     boolean FALSE sga_max_size     big integer 804M sga_target     big integer 0 SQL> show parameter max_target NAME     TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_max_target     big integer 804M SQL> show parameter memory NAME     TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address     integer 0 memory_max_target     big integer 804M memory_target     big intege...