SQL> select value from v$parameter where name='background_dump_dest'; VALUE -------------------------------------------------------------------------------- /u0/app/oracle/diag/rdbms/orcl/orcl/trace $ cd /u0/app/oracle/diag/rdbms/orcl/orcl/trace $ ls alert_orcl.log orcl_j001_25686.trm orcl_ora_20641.trc
or in SQL *PLUS
SQL> show parameter background_dump_dest;
SQL> select value from v$parameter where name='diagnostic_dest'; VALUE -------------------------------------------------------------------------------- /u0/app/oracle
To know the current ORACLE_SID, you can use following select:
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- ORCL
Alert log file consist alert_.log
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 24 12:32:12 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Connected.
SQL> select status from v$instance;
select status from v$instance;
*
ERROR at line 1:
ORA-01012: not logged on
“The ORA-01012: not logged on” error occurred due to heavy load in the database. If there is maximum number of sessions connected to the database, database does not allow sysdba privileged user as well as other users are connected to the database.
Solution 1: First Check /etc/hosts file if there is incorrect host name or IP correct it and try Start database.
Solution 2: Without shutdown anything just kill that database processes which are not local processes. Local means background processes.
ps -ef | grep $ORACLE_SID | grep -v grep | grep "LOCAL=NO" | awk '{print $2}' | xargs kill -9
Solution 3: Shut down application server sessions
Step 1. Create Password file for ASM Instance
Step 1. Set $ORACLE_HOSTNAME to the old hostname
export ORACLE_HOSTNAME=old_name
Step 2. De-configure the old DB Console by running the following command:
$ORACLE_HOME/bin/emca -deconfig dbcontrol db
Step 3. $ORACLE_HOSTNAME to the new hostname:
export ORACLE_HOSTNAME=new_name
Step 4. Configure the new DB Console
$ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate