Startup Fails With ORA-01012: Not Logged On

Startup Fails With ORA-01012: Not Logged On

Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running ORACLE - shut it down first.

$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 12 07:53:11 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;
ERROR at line 1:
ORA-01012: not logged on
SQL> desc v$instance
ERROR:
ORA-01012: not logged on

SQL> startup
ORA-01012: not logged on

Changes:
Oracle has been forcefully shutdown at OS level or crashed.

Cause:
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.

$ sysresv

IPC Resources for ORACLE_SID "TEST" :

Shared Memory:
ID              KEY
5963794         0x00000000
5996563         0x00000000
6029332         0xb2e3c9ac

Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "TEST"


Solution:

On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

$ ipcrm -m 5963794
$ ipcrm -m 5996563
$ ipcrm -m 6029332

$sqlplus '/as sysdba'

Connected to an idle instance.

SQL> startup Oracle instance started
Total System Global Area   10689474560 bytes
Fixed Size                     2237776 bytes
Variable Size               6375344816 bytes
Database Buffers            4294967296 bytes
Redo Buffers                  16924672 bytes
Oracle Instance Started.
Oracle Database Opened.

Reference metalink Doc ID 1365419.1

All Checks

All Checks

Database Version

SQL> desc v$version

SQL> select banner from v$version;

To check the EBS version

external hard disk mount
mount -t ntfs-3g /dev/sdb1 /prasad123

For unmount
umount -t ntfs-3g /dev/sdb1 /prasad123

create password file at dbs
orapwd file=orapwTESTC password=sys@123 ignorecase=y

Log as sysdba using created password file for test purpose

sqlplus sys/sys@123@TEST as sysdba

To check the ORACLE_HOME & ORACLE_SID

$ echo $ORACLE_HOME
$ echo $ORACLE_SID

To chec SID in Applications

$ echo $TWO_TASK

To unlock a database user

CONN / AS SYSDBA

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

To create a directory

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

To get the applicatoin url page

select home_url from apps.icx_parameters;

select home_url from icx_parameters;

grep -i s_login $CONTEXT_FILE

Datapump related queries

select * from dba_datapump_jobs;

SYNTAX WE USE as a DBA

CREATING AN SPFILE:
           
create SPFILE=’</Source path>’ from PFILE=’</Destination path>’;

ALTERING THE DATABASE:

alter database<Database name> NOMOUNT;
alter database<Database name> MOUNT;
alter database<Database name> OPEN;

OPENING THE DATABASE IN READONLY MODE:

alter database<Database name> open READONLY;

OPENING DATABASE IN RESTRICTED MODE:

startup RESTRICT;
alter system enable RESTRICTED session;

MULTIPLEXING CONTROL FILES USING ‘SPFILE’:

alter system set controlfile=’</Source path>’ ‘</Destination path>’ scope=spfile;
shut down database;
copy controlfiles using O.S command.
==>cp ‘</source path>’ ‘</destination path>’
startup database

MULTIPLEXING CONTROL FILES USING ‘PFILE’:

Shut the database;
copy controlfiles using O.S command.
==>cp ‘</source path>’ ‘</destination path>’
Edit the pfile with new control file locations.
startup the database

FORCING A LOG SWITCH:

alter system switch logfile;

ADDING REDO LOG ‘GROUPS’:

alter database ADD logfile GROUP 1 (‘</Path of logfile to be placed>’ ‘</ another logfile to be placed>’) Size 1M;

ADDING ONLINE REDO LOG FILE ‘MEMBERS’:

alter database ADD logfile MEMBER ‘</Path of logfile>’ to GROUP 1, ‘</Path of logfile>’ to GROUP 2, ‘</Path of logfile>’ to GROUP 3;

DROPPING REDO LOG ‘GROUPS’:

alter database DROP logfile GROUP <group no.>;

DROPPING REDO LOG ‘MEMBERS’:

alter database DROP logfile MEMBER ‘</Path of log file>’;

RELOCATING (OR) RENAMING LOGFILES:

Copy redo log files to new location.

== > cp ‘</source path>’ ‘</Destination path>’

alter database clear logfile ‘</Path and new name>’; Drop old members;

CREATING TABLESPACE:

create tablespace <tablespace name> DATAFILE’</Path>’ size <no.> Autoextend on next<size.> maxsize <size>;

CREATING LOCALLY MANAGED TABLESPACES:

create tablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.> Extent management local uniform size <no.>;

CREATING UNDO TABLESPACES:

create undotablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.>;

CREATING TEMPORARY TABLESPACES:

create temporary tablespace <tablespace name> TEMPFILE ‘</Destination>’ size<no.> Extent management local uniform size<no.>;

SETTING DEFAULT TEMPORARY TABLESPACES:

alter database default temporary tablespace <tablespace name>;

SETTING TABLESPACE IN ‘READONLY’ MODE:

alter tablespace <tablespace name> READONLY;

TAKING TABLESPACE OFFLINE AND ONLINE:

alter tablespace <tablespace name> OFFLINE;
alter tablespace <tablespace name> ONLINE;

MANUALLY RESIZING DATAFILE:

alter database DATAFILE ‘</path>’ RESIZE <NO.>;

ADDING DATAFILE TO TABLESPACE:

alter tablespace <tablespace name> ADD DATAFILE ‘</path>’ size <no.>;

METHODS OF MOVING(RENAMING) DATAFILES:

Take tablespace offline.
use O.S command to copy datafile to different location.
alter database ‘</path>’ RENAME Datafile ‘</Source path>’ to ‘</Destination path>’;
Bring tablespace online;

IF TABLESPACE CANNOT BE TAKEN OFFLINE FOR MOVING:

Shut down database.
use O.S command to copy datafile to different location.
Mount database.
alter database ‘</path>’ RENAME Datafile ‘</Source path>’ to ‘</Destination path>’;
open database.

TABLESPACE DROPPING:

drop tablespace <tablespace name> including contents and datafiles;

AUTOMATIC SEGMENT SPACE MANAGEMENT:

create tablespace <tablespace name> DATAFILE ‘</path>’ size<no.> Extent management local uniform size <no.> Segment space management AUTO;

SWITCHING UNDO TABLESPACE:

alter system set UNDO_TABLESPACE=<undo name>;

DROPPING UNDO TABLESPACE:

drop tablespace <undo tablespace name>;

MANUALLY ALLOCATING EXTENTS:

alter table <tablespace name> Allocate extent (size<no.> DATAFILE ‘</path>’);

MOVING ONE TABLE TO OTHER TABLESPACE:

alter table <table name> Move tablespace<tablespace name>;

TRUNCATING TABLE:

truncate table <table name>;

DROPPING TABLE:

drop table <table name> cascade constraints;

DROPPING COLUMNS:

alter table<table name> drop column comments Cascade constraints checkpoint <1000>;

CREATE BITMAP INDEX:

create BITMAP index < tablename_column_name_idx > On <tablename>(column name) Pctfree 30 Storage(initial <no.> next <no.> Pct increase 0 maxextents <no.>) Tablespace <tablespace name>;

REBUILDING INDEX:

alter index <index name> REBUILD Tablespace<tablesapce name>;

REBUILDING INDEXES ONLINE:

alter index <index name> REBUILD ONLINE;

DROPPING INDEX

drop index <index name>;

Tablespace related queries:

To check temporary tablespace

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

To resize temporary tablespace

alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp01.dbf' resize 100M;

To add a datafile to temporay tablespace

ALTER TABLESPACE temp1 ADD TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' SIZE 4096M;

Resize the datafile of the particular tablespace.

ALTER DATABASE DATAFILE '/d01/oracle/VIS/db/apps_st/data/data01.dbf' RESIZE 2048M;

Add a datafile to tablespace

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/d01/oracle/VIS/db/apps_st/data/data02.dbf' SIZE 1024M;

Set Default Temporary Tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

To resize temporary tablespace:

ALTER DATABASE TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' RESIZE 4096M;

Create a New User and Grant

Login to SYS user.

CREATE USER XXCUST IDENTIFIED BY ORACLE
DEFAULT TABLESPACE XXCUST_TABLESPACE TEMPORARY TABLESPACE TEMP;

GRANT RESOURCE, CONNECT TO XXCUST;

To view current users in Oracle database and their current status:

select username, account_status from dba_users;

Rename a table in Oracle

alter table <Current table name> rename to <New table name>;

Get the objects consumed by SYSAUX tablespace

SELECT * FROM V$SYSAUX_OCCUPANTS

How to check the last resetlogs

SQL> select name,to_char(RESETLOGS_TIME,'DD-MON-YYYY HH:MI::SS') from v$database;

How to find a table size on Oracle Database

SELECT segment_name, tablespace_name, ROUND((BYTEs/1073741824*100)/100) sizeGB, ROUND((BYTEs/1048576*100)/100) sizeMB, ROUND((BYTES/1024*100)/100) sizeKB FROM USER_SEGMENTS order by sizeKB desc;

To check the hidden parameters in a database

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

To check SGA and PGA sizes

SELECT * FROM V_$SGA_TARGET_ADVICE;
SELECT * FROM V_$PGA_TARGET_ADVICE;
or
select * from v$memory_target_advice;

LOGIN PAGE appears blank in Oracle Applications R12

LOGIN PAGE appears blank in Oracle Applications R12

Issue:

When logging onto E-Business Suite front end page, the login page appears blank and nothing loads; Unable to access login Page in Oracle APPS R12

Cause:
Issue is caused by missing class files on the OA_HTML directory.

Temporary Fix:
1. Shutdown the application services
2. Compile the jsp’s manually using the below command
perl $FND_TOP/patch/115/bin/ojspCompile.pl –compile –flush -p 2
3. Restart the application services
4. Retest the issue.

Permanent Fix:
1. Set the parameter “s_jsp_main_mode” in XML file from “just run” to “recompile”
<jsp_debug_parameters oa_var=”s_jsp_main_mode”>justrun</jsp_debug_parameters>
change it to  <jsp_debug_parameters oa_var=”s_jsp_main_mode”>recompile</jsp_debug_parameters>

2. Execute autoconfig to apply the change done in XML File. Autoconfig will update file $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml

3. Re-start the applications and test the issue.

Note: However it is not recommended to change the default value of parameter s_jsp_main_mode (i.e. just run) as it may lead to performance issue.

Reference metalink doc ID 1491845.1

The system has not been taken off maintenance mode completely.Please contact your system administration.

The system has not been taken off maintenance mode completely.Please contact your system administration.

The system has not been taken off maintenance mode completely.Please contact your system adminstration.

Cause: After patch application, Maintenance mode is not disabled.

In my case, I encountered this error while accessing oracle E-business suite, I started the applications before disabling Maintenance mode which is incorrect.

Solution :

1) Login to applmgr user

2) Stop apache 

3) Run adadmin

4) use option 5 to disable maintenance mode

AD Administration Main Menu

————————————————–

1.Generate Applications Files menu

2.Maintain Applications Files menu

3.Compile/Reload Applications Database Entities menu

4.Maintain Applications Database Entities menu

5.Change Maintenance Mode

6.Exit AD Administration

Enter your choice [6] : 5

Please select an option:

1.Enable Maintenance Mode

2.Disable Maintenance Mode

3.Return to Main Menu

Enter your choice [3] : 2

sqlplus -s &un_apps/***** @/apps/vision/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql DISABLE

Successfully disabled Maintenance Mode.

5) Restart Apache. 

We can even  enable or disable maintenance mode without using adadmin, 

ENABLE maintenance mode:

sqlplus -s apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

DISABLE maintenance mode:

sqlplus -s apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

Check the current status of Maintenance Mode:

select fnd_profile.value(‘APPS_MAINTENANCE_MODE’) from dual;

MAINT     =  MAINTENANCE MODE is ENABLED

NORMAL =  MAINTENANCE MODE is DISABLED

CatBundle.sql

CatBundle.sql

What is a Catbundle.sql script and it’s purpose after patching (Part of PSU Post-Patching) ?

Before asnwering about the role or importance of catbundle.sql script i would like to explain about what a database bundle is.
– A database bundle series is a sequence of patches where each patch in the series includes the contents of the previous patch in the series.

Installation of the database changes is done by catbundle.sql, which takes input from an XML file named bundledata_<bundle series>.xml.
catbundle.sql determines the last bundle applied to the database and executes only the scripts in the patch that have changed since the last bundle patch was applied.

Opatch tool replaces software files but does not modify database catalog. Applying a patch set update requires modifying the catalog. There is an sql script file named “catbundle.sql” under “$ORACLE_HOME/rdbms/admin” directory. Execute that script as sysdba to make necessary modification to the catalog.

Patch your ORACLE_HOME before you start and execute the catbundle script.

Once done follow below provided steps if patching (PSU Track).

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

sql>@catbundle.sql psu apply

Once you’ve executed the catbundle.sql means your database is fully patched.

Oracle Inventory

Oracle Inventory

The inventory is a very important part of the Oracle Universal Installer. This is where OUI keeps all information regarding the products installed on a specific machine.

There are two inventories with the newer releases of OUI (2.x and higher):

-The inventory in the ORACLE_HOME (Local Inventory)
-The central inventory directory outside the ORACLE_HOME (Global Inventory)

At startup, the Oracle Universal Installer first looks for the key that specifies where the global inventory is located at (this key varies by platform).

/var/opt/oracle/oraInst.loc (typical)
/etc/oraInst.loc (AIX and Linux)
HKEY_LOCAL_MACHINE -> Software -> Oracle -> INST_LOC (Windows platforms)
If this key is found, the directory within it will be used as the global inventory location.

If the key  is not found, the inventory path will default created as follows:

UNIX : ORACLE_BASE\oraInventory
WINDOWS : c:\program files\oracle\Inventory
If the ORACLE_BASE environment variable is not defined, the inventory is created at the same level as the first Oracle home. For example, if your first Oracle home is at /private/ORACLEHome1, then, the inventory is at /private/oraInventory.

With Oracle Applications 11i the inventory contains information about both the iAS and RDBMS ORACLE_HOMEs

About the Oracle Universal Installer Inventory

The Oracle Universal Installer inventory is the location for the Oracle Universal Installer's bookkeeping. The inventory stores information about:

All Oracle software products installed in all Oracle homes on a machine
Other non-ORACLE_HOME specific products, such as the Java Runtime Environment (JRE)

Starting with Oracle Universal Installer 2.1, the information in the Oracle Universal Installer inventory is stored in Extensible Markup Language (XML) format. The XML format allows for easier diagnosis of problems and faster loading of data. Any secure information is not stored directly in the inventory. As a result, during deinstallation of some products, you may be prompted for required secure information, such as passwords.

By default, the Universal Installer inventory is located in a series of directories at /Program Files/Oracle/Inventory on Windows computers and in the <Oracle Home>/Inventory directory on UNIX computers.

-Local Inventory

There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME.
The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.

If the Local Inventory becomes corrupt or is lost, this is very difficult to recover, and may result in having to reinstall the ORACLE_HOME and re-apply all patchsets and patches.

-Global Inventory

The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc.
The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs.
The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone.

Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.

OUI 2.x (11.5.7 and later):

Global inventory becomes a list of pointers to each Local Inventory
Global and local inventories are in text based XML format
XML conversion is validated and performed if needed by Rapid Clone
Prior to OUI 2.x (11.5.5 and earlier):

Global inventory was in binary format
Global inventory contained all software install information
No local inventories in ORACLE_HOMEs
If the Global Inventory is lost or becomes corrupt, but the Local Inventory remains, it may be possible to recreate the Global Inventory by following Note: 295185.1

Reference metalink Doc ID 360079.1