OBIEE 11G: Unable to login to OBIEE 11G Analytics due to error: WSM-06162

After installation of OBIEE 11G, I had trouble logging into Analytics using WebLogic credentials. Thankfully the friendly folks at OTN forum helped me out.

Once you have OBIEE 11G installed, you should be able to open up

http://host-port/analytics

and access Analytics with your WebLogic credentials. In case you can't, check your WebLogic console (running on command prompt) and see if you get WSM-06162 error which looks like:


WSM-06162 The policy referenced by URI "oracle/wss_username_token_service_policy" could not be retrieved as connection to Policy Manager cannot be established at "t3://AI-PC:7001" due to invalid configuration or inactive state

Please note, its assumed that your WebLogic credential is working on http://host:port/console and http://host:port/em.

1) Login to Oracle Enterprise Manager OEM by going to: http://host:port/em

2) Login with your WebLogic credential.

3) Expand the Internal Applications and check the status of wsm-pm.

4) If its a red down arrow, right-click on wsm-pm.

5)  Select Control >> Start Up to start it up.

6) wsm-pm should start up. Just note, it may require database access. Ensure your database is running. In my case my OracleXEClrAgent wasn't running.

7) Try restarting BI Services.

8) You should be able to login in now.

SOURCE:
https://forums.oracle.com/forums/thread.jspa?threadID=2327340&tstart=0

 http://docs.oracle.com/cd/E21764_01/core.1111/e10113/chapter_wsm_messages.htm#sthref26556

OBIEE 11G: weblogic.security.SecurityInitializationException

Recently I installed OBIEE 11G in my laptop (simple install) with Windows 7. When I start BI Services from START, my weblogic throws this error before shutting down:  

weblogic.security.SecurityInitializationException: Authentication denied: Boot identity not valid; The user name and/or password from the boot identity file (boot.properties) is not valid. The boot identity may have been changed since the boot identity file was created. Please edit and update the boot identity file with the proper values of username and password. The first time the updated boot identity file is used to start the server, these new values are encrypted.  

First Weblogic log file is located at:
C:\BI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\logs

During installation I configured to put all my BI stuff in BI11g folder in C: drive. Hence this is the root folder of my BI installation. Anyway, this log files tells the errors above in bold, in more readable format than digging through tonnes of lines in command prompt.

I Googled around and came across someone in OTN forum who had the same issues. Turns out there may be something wrong in some boot.properties file.

The boot.properties is located at:  
C:\BI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security

This file only has 3 lines the 2nd and 3rd being the weblogic username and password. I had a stray "/" in password which I removed.

Then I restarted the BI Services and it works now. If you go back to your boot.properties files again, you'll notice that your Weblogic username and password is now hashed.

SOURCE:
https://forums.oracle.com/forums/thread.jspa?threadID=2241882

Connecting to Oracle Database in SQL plus

In SQLPlus, log into your database and then run the command with the following syntax:


sql> connect username/password@ipaddress:port/sid

So in if you had a XE database over local network at 10.0.0.143, find out the port number and SID (usually XE for Express Edition). Then you would connect as:

  sql> connect sys/secretpassword@10.0.0.143:1551/XE

Useful Oracle SQL commands

This blog entry will be updated as I come across more common useful SQL queries.

View current schema:

select sys_context('userenv','current_schema') from dual;




Change Schema: alter session set current_schema=name_of_your_schema;



Grant access to tablespace to user (before a user created is allowed to create a table). grant all on all_tables to sys;



List of users who have privilege to create database linkSELECT * FROM dba_sys_privs WHERE privilege=’CREATE DATABASE LINK’
and admin_option=’YES’;


Grant permission to create database link privilegeGRANT CREATE DATABASE LINK TO sys WITH ADMIN OPTIONS



Unlock a schema or user accountALTER USER schema_name account unlock; 

Importing Database from a DMP file

imp schema_name/schema_password IGNORE=Y FILE=EXP_SCHEMA_NAME_SCHEMA.DMP LOG=IMPORT_LOG_FILE.log

Adding existing tabset to your page

If you have a page without tabset (you didn't include during creation by accident) you can easily add it again.
To view a list of pages without tabs, go to Home >> Shared Components >> Tabs (under Navigation)
In the next page there is a summary telling you how many tabs are there in the set etc. At the bottom of the section (or region) it tells you pages without tabs. In my case I have:


Pages with no tabs:
21, 24, 101

101 is login page so I wouldn't bother adding tabs there. But I accidentally didn't include tabs in page 21 and 24.

To add tabs:

1) Go to page definition of the page without tab. In my case it's 21 and 24.

2) Click on Application default under Page Rendering >> Page >> Template.

3) Scroll down or click on the section "Display Attribute".

4) Under Standard Tab Set select list should be the tabs you created. Select and Apply Changes.

Your existing tabset should now appear on the page.

Get a count of all objects in your schema

Pre-requisite: Must have access to schema you are querying on.

select owner,
count(object_type) "count", object_type
from ALL_OBJECTS
where owner = 'schema_name'
group by owner, object_type;


----

Replace schema_name with the name of your schema (in upper case). 

Lost APEX ADMIN password?

You need to locate and run the file C:\oraclexe\app\oracle\product\11.2.0\server\apex\apxchpwd.sql

If you are running your database in Linux, find the apxchpwd.sql file by

$ locate apxchpwd.sql

Then go to that directory and run SQLPlus.

Follow these instructions to recover your ADMIN password. Note this is not for reseting your workspace ADMIN account password. Its for resetting the password for ADMIN account for your entire APEX installation.

1. Open command prompt and cd to C:\oraclexe\app\oracle\product\11.2.0\server\apex
2. Run sqlplus from command prompt.
3. Run the command alter session set current_schema='apex_040000';

Run the command @apxchpwd.sql and enter your password.

You should now be able to log into http://localhost:8080/apex/apex_admin. APEX will ask you change  your password again after logging in.

Exporting Schema in Oracle Apex

You can export Schema from Oracle APEX by following these steps:

1) Head over to SQL Workshop >> Utilities >> Generate DDL

2) Click on Create Script

3) Pick the Schema

4) Check schema objects you wish to import or check all

5) Click on Genereate DDL