"ORA-01075: you are currently logged on" with ORA-4031

Version: 

Oracle 11g EE 11.2.0.3.0 - 64bit Production


Issue:

ORA-01075: you are currently logged on after getting ORA-4031

Cannot login to the SQLPlus as sysdba.


60-oracle@DBServer1=> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 01:04:36 2014

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

ERROR:
ORA-01075: you are currently logged on


Enter user-name: sys as sysdba
Enter password: 
ERROR:
ORA-01075: you are currently logged on


Verification:

The alert log file had so many errors related to ORA-4031

ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select file#, block#, ts# fr...","Typecheck","kgghtInit")

I was able to login as system user. Tried to flush the shared_pool, but ended up getting memory error

SQL> alter system flush shared_pool;
alter system flush shared_pool
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
pool","select t.ts#,t.file#,t.block...","sga heap(1,0)","kglsim object batch")


Solution:


We need to solve the ORA-4031 issue first. Once ORA-4031 issue is resolved, the ORA-1075 will be resolved by itself.

ORA-1075 is because of the after effect of ORA-4031. The only work around is to connect as system user and flush the shared_pool. If that is also not working, then kill the background process, restrat the DB and increase shared pool. The steps are mentioned below

Step 1. Identify and kill the DB background process

65-oracle@DBServer1=> ps -ef|grep pmon|grep orcl
oracle   22255     1  0 Mar18 ?        00:00:07 ora_pmon_orcl

kill -9 22255

Step 2. Connect to DB and start the instance

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 01:25:05 2014

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

Connected to an idle instance.

SQL> 
SQL> 
SQL> startup

Step 3. Increase the shared pool size

Increase sga_target size or shared_pool_size parameter.

12C: EM Express for CDB/PDB/Non-CDB

EM Express:

EM express is a web based light weight tool that will help in managing Oracle 12C databases.  It offers support for basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning. To manually set up EM Express, simply configure the HTTPS or HTTP port by logging into the database and setting the port using dbms_xdb_config.sethttpsport (https) and dbms_xdb_config.sethttpport (http)

Requirement:

To setup and access EM Express for  all three types of 12C databases

1) CDB

2) PDB

3) Non-CDB

1) EM express for CDB

Get the port number on which EM express is configured.

You can find it out either from lsnrctl status or using SQL query as shown below.

lsnrctl status | grep http

...........

DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=MyHost.oracle.com)(PORT=5500))(Security=(my_wallet_directory=MyWallet_Dir_Path))(Presentation=HTTP)(Session=RAW))

OR

Login to CDB as SYS user and issue the following query

select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT

-------------------

5500

We can then access the EM express by entering following URL in the browser

https://<hostname>:5500/em

If the port is not configured, then we can do so by setting the HTTP/HTTPS port. 

SQL> exec dbms_xdb_config.sethttpsport(5500);

2) EM express for PDB

Login to PDB and then configure a port. In the example my PDB name is PDB1.

SQL> alter session set container=pdb1;

sho con_name

CON_NAME

------------------------------

PDB1

Configure the port for EM express

Here we are going to configure port number 5501 for EM express

SQL> exec dbms_xdb_config.sethttpsport(5501);

Confirm that EM has been configured on specified port (1501)

SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT

------------

5501

Access  EM express by entering following URL in the browser:

https://<hostname>:5501/em

3) EM Express for Non-CDB

Login to non-CDB. The container name would come as Non consolidated in case of Non-CDB

SQL> conn sys/pwd as sysdba

sho con_name

CON_NAME

------------------------------

Non Consolidated

Get the port configured for EM Express with following SQL query

SQL > select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT

------------------------

5502

Access  EM express by entering following URL in the browser:

https://<hostname>:5502/em

Note:

Above method of configuring EM Express will only work if DB is registered with the default listener running on port 1521. If the DB is registered with the non-default listener then you need to add an entry for the listener in tnsnames.ora file and also set init parameters LOCAL_LISTENER (to the non default listener) and DISPATCHERS  

local_listener=<host_name>:<port no>

MDS-01329: unable to load element "persistence-config" in ADF 11g

Version:

WLS: 10.3.6
ADF: 11.1.1.6.0

Issue:

Issues on starting MDS data source when wsm-pm is deployed in to a Managed server. The Managed server startup was failing.

The Managed server startup log had following inforation

Feb 04, 2014 7:54:33 AM oracle.adf.share.config.ADFMDSConfig parseADFConfiguration
SEVERE: MDS Configuration Exception encountered in parse ADF ConfigurationMDS-01330: unable to load MDS configuration document    
MDS-01329: unable to load element "persistence-config"
MDS-01370: MetadataStore configuration for metadata-store-usage "OWSM_TargetRepos" is invalid.    
MDS-00929: unable to look up name "jdbc/mds/owsm" in JNDI context    
javax.naming.NameNotFoundException: While trying to lookup 'jdbc.mds/owsm' didn't find subcontext 'jdbc'. Resolved ''; remaining name 'jdbc/mds/owsm'

Caused By: oracle.mds.exception.MDSExceptionList: MDS-01329: unable to load element "persistence-config"
MDS-01370: MetadataStore configuration for metadata-store-usage "OWSM_TargetRepos" is invalid.    
MDS-00929: unable to look up name "jdbc/mds/owsm" in JNDI context    
javax.naming.NameNotFoundException: While trying to lookup 'jdbc.mds/owsm' didn't find subcontext 'jdbc'. Resolved ''; remaining name 'jdbc/mds/owsm'

        at oracle.mds.config.PConfig.loadFromBean(PConfig.java:1020)
        at oracle.mds.config.PConfig.<init>(PConfig.java:775)
        at oracle.mds.config.MDSConfig.loadFromBean(MDSConfig.java:1254)
        at oracle.mds.config.MDSConfig.loadFromElement(MDSConfig.java:1326)
        at oracle.mds.config.MDSConfig.<init>(MDSConfig.java:884)
        Truncated. see log file for complete stacktrace

Action:

We can verify couple of this to make sure the setup done is correct

1. The "wsm-pm" deployment should only be targeted to the managed server. If it has Admin server also, uncheck it from its deployment target.

2. Another reason and possible reason if the wsm-pm only has Managed server as its target is when the JDBC data source mds-owsm is not targeted to  the managed server.  Add the Managed server to its target list.


Note: Also in Fusion Middleware 12c Infrastructure, we might have to add the data source opss-data-source also as a target to the Managed Server to access wsm-pm URL properly.

Files on system are patched but Inventory Update has failed

Scenario:

Patching an Oracle 10g AS environment using opatch utility.

Platform: Windows

Error:

Updating inventory...
OPATCH_JAVA_ERROR  : Wrong number of arguments
Files on system are patched but Inventory Update has failed.  Please run 'opatch
 lsinventory' to check if the patch has been recorded in Inventory.(1)


Verifying patch...
OPATCH_JAVA_ERROR: Wrong number of arguments.  VerifyPatch needs ORACLE_HOME, OUI location, patch location, patch ID and path to 'ar' command, no_inventory, OS_ID.

java.lang.Exception: Wrong number of arguments.  VerifyPatch needs ORACLE_HOME,OUI location, patch location, patch ID and path to 'ar' command, no_inventory, OS_ID.        at opatch.VerifyPatch.main(VerifyPatch.java:803)
Exception in thread "main" Verification of the patch failed.

ERROR: OPatch failed as verification of the patch failed.
OPatch returns with error code = 240

Verification:

The patch application was succesfull, but updating the inventory was not happening. 
opatch lsinventory command failed to report the new patch.

Fixing:

The directory  location at which the patch was placed had space(" ") in it. 
For eg: I staged the patche under "C:\Stage\AS Softwares".

It is advisable to keep patch in a directory with out any space in its name.

I later moved the patch to C:\Stage and extracted it.

Later the patch application was succesfull.

In-place upgrade OBIEE 11.1.1.6.0 to 11.1.1.7.0

Requirement:

Upgrade OBIEE 11.1.1.6.0 to 11.1.1.7.0 in Linux


High level Steps:

It includes 3 high level steps mainly

1. OBIEE S/W upgrade
2. Upgrade repository schemas
3. Upgrade domain


Steps in detail:

1. Pre-Upgrade Steps

Stop all the BI components (AdminServer, Managed Server, opmn components and Nodemanager)

Take a backup of MW Home and Inventory

2. OBIEE S/W Upgrade

Download OBIEE 11.1.1.7.0 for the platform

Invoke runInstaller

Intallation Type Screen: Choose Software Only Install

Intallation Location Screen:The installer would automatically choose the current MW and BI installation directories.

Click Intall

3. Upgrade MDS and BIPLATFORM schemas using Patch Set Assistant (psa)

psa is available in $MW_HOME/oracle_common/bin

Invoke psa (./psa)

Select Component Screen: Choose only "Oracle Bussiness Intelligence". Both BIPLATFORM and MDS schema would be selected by default

Prerequisites Screen: Select both prerequisite check boxes 1. backup completed and 2. DB version certified by FMW upgrade

MDS Schema Screen: Give DB connection information and DB user "SYS as sysdba" and press "Connect" button
Give MDS and BIPLATFORM username and password.

Examine Screen: The status of the examination process would be shown in next page. Both status should be of status "Succeeded".
Click Upgrade button

4. Start nodemanager and AdminServer

Start nodemanager and Admin server. This is mandatory since during domain updation both Admin server and bi server has to be retsrated by the configuration utility.


5. Domain Upgrade using config utility

Invoke configuration utility from $MW_HOME/Oracle_BI1/bin folder

./config.sh

Create, Scale out or Extend Screen: Choose Update BI Domain option
Provide the Adminserver hostname, port number and user details

Update BI Domain Details Screen: Verify that all the paths chosen by the installer are accurate.

Click configure and Press Finish button when done

The Configuration Assistant will restart AdminServer and start the managed server, and OPMN components.

Verify that all the URLs are reachable and all components are up.

Weblogic 10.3.5 to 10.3.6 in-place upgrade

Action:

Download and apply the upgrade patch

Steps

1. Download the upgrade patch from metalink. The patch number is 13529623. 

You can also search for the correct patch in metalink using the "Patches &   Updates" tab

In the "Patch Search" box provide the following details 

Oracle WebLogic Server as Product, 10.3.6 as Release, Linux X86 as Platform (Linux 64 bit was my platform) and  give Upgrade in the Description field to reduce the number of search results.

2. Unzip the downloaded file. 
The resultant file would be wls1036_upgrade_generic.jar file.

3. Backup MW folder and Inventory directory
Shutdown all the WLS services and backup the directories. Always good to     have the backup in place before a major upgrade.

4. Invoke the upgrade installer and follow the intrauctions in the installer
java -d64 -jar wls1036_upgrade_generic.jar

5. Choose "Use and existing middleware home" option and pick the current MW Home location for upgrade. All the screens are self explanatory.

6. Click complete.
Files will now be upgraded to 10.3.6.0 Maintenance level

7. Startup the Weblogic Admin server
The startup log will show the latest maintenance level of 10.3.6.0

Get user creation metadata and all roles/privileges granted to the user

Requirement:

Create a database user in another DB based on the user available in the source DB with the same object/DB level grants.

DB Version: 11.2.0.2.0

Steps

1. Find the User creation script from source database

Extract the DDL used for the user creation using dbms_metadata

SQL> select dbms_metadata.get_ddl('USER','MYUSER') from dual;

DBMS_METADATA.GET_DDL('USER','MYUSER')

---------------------------------------------------------

   CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:2526C5A32BD6A196B2A

2F2CC6F5E88D4A5932593709E4CC838BFDC036C27;C84965A9EC7311F4'

      DEFAULT TABLESPACE "USERS"  TEMPORARY TABLESPACE "TEMP"

2. Get system privileges granted to the user using dba_sys_privs

SQL> select PRIVILEGE from dba_sys_privs where GRANTEE='MYUSER';

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

3. Get the roles granted to the user using dba_role_privs

SQL> select GRANTED_ROLE from dba_role_privs where GRANTEE='MYUSER';

GRANTED_ROLE

------------------------------

CONNECT

DBA

4. Get all object grants of the user using dba_tab_privs

SQL> select OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE='MYUSER';

no rows selected