Category Archives: OBIEE

Changing weblogic password in OBIEE environment

If you want to change the weblogic password in an OBIEE environment you have to be very careful so that both the Weblogic and Managed Server are running when you change the password. If only the Weblogic is started when you change the password then the Managed Server won’t start with any password (old or new). The error it throws is:

<BEA-090403> <Authentication for user weblogic denied>
<BEA-000386> <Server subsystem failed. Reason: weblogic.security.SecurityInitializationException: Authentication for user weblogic denied
   weblogic.security.SecurityInitializationException: Authentication for user weblogic denied
   at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.doBootAuthorization(CommonSecurityServiceManagerDelegateImpl.java:965)
   at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initialize(CommonSecurityServiceManagerDelegateImpl.java:1050)
   at weblogic.security.service.SecurityServiceManager.initialize(SecurityServiceManager.java:873)
   at weblogic.security.SecurityService.start(SecurityService.java:141)
   at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
   at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
   at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)

It took me a while to figure this out, maybe it will save somebody some debugging time.

Partitioning S_NQ_ACCT and S_NQ_DB_ACCT

With OBIEE you can Enable Usage Tracking to see a lot of interesting facts about who accessed what dashboard, how long a query took, error messages and a lot of other facts. All this is stored in S_NQ_ACCT and from OBIEE 11.7.1.0 in S_NQ_DB_ACCT. The last one is used when your database is stored in a exalytic or exalogic box for better performance .

If you have a lot of users, these two tables can get kind of big (because of a column in each table that is a CLOB type). For us with almost somewhere around 4000-5000 users a month, in 3 months we got the table usage to almost 40G. For some this might not be a problem, but if you multiply this by 4 you get 160G a year.

If you don’t care about the data you can just disable the FK and issue a truncate command on the columns. We wanted to keep the last month worth of data and delete the rest. Because there is a CLOB column, a simple delete will not suffice to free the disk space. That’s why we choose to partition the table by range, so the data corresponding to one day will be in one partition. That way we will only drop the days we want and easily free the space. The to script I made that will partition the tables and also keeping the data are below. Keep in mind that this scripts copy the data from the old table to the new one, so you will need to have enough space in the tablespace and disk for this.

-- Partition S_NQ_ACCT
-- Create partitioned table S_NQ_ACCT_TMP
CREATE TABLE "S_NQ_ACCT_TMP"
(
"USER_NAME" VARCHAR2(128 BYTE),
"REPOSITORY_NAME" VARCHAR2(128 BYTE),
"SUBJECT_AREA_NAME" VARCHAR2(128 BYTE),
"NODE_ID" VARCHAR2(50 BYTE),
"START_TS" DATE,
"START_DT" DATE,
"START_HOUR_MIN" CHAR(5 BYTE),
"END_TS" DATE,
"END_DT" DATE,
"END_HOUR_MIN" CHAR(5 BYTE),
"QUERY_TEXT" VARCHAR2(1024 BYTE),
"QUERY_BLOB" CLOB,
"QUERY_KEY" VARCHAR2(128 BYTE),
"SUCCESS_FLG" NUMBER(10,0),
"ROW_COUNT" NUMBER(20,0),
"TOTAL_TIME_SEC" NUMBER(10,0),
"COMPILE_TIME_SEC" NUMBER(10,0),
"NUM_DB_QUERY" NUMBER(10,0),
"CUM_DB_TIME_SEC" NUMBER(10,0),
"CUM_NUM_DB_ROW" NUMBER(20,0),
"CACHE_IND_FLG" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
"QUERY_SRC_CD" VARCHAR2(30 BYTE) DEFAULT '',
"SAW_SRC_PATH" VARCHAR2(250 BYTE) DEFAULT '',
"SAW_DASHBOARD" VARCHAR2(150 BYTE) DEFAULT '',
"SAW_DASHBOARD_PG" VARCHAR2(150 BYTE) DEFAULT '',
"PRESENTATION_NAME" VARCHAR2(128 BYTE) DEFAULT '',
"ERROR_TEXT" VARCHAR2(250 BYTE) DEFAULT '',
"IMPERSONATOR_USER_NAME" VARCHAR2(128 BYTE) DEFAULT '',
"NUM_CACHE_INSERTED" NUMBER(10,0) DEFAULT NULL,
"NUM_CACHE_HITS" NUMBER(10,0) DEFAULT NULL,
"ID" VARCHAR2(50 BYTE)
)
PARTITION BY RANGE
(
START_DT
)
INTERVAL
(
NUMTODSINTERVAL(1,'DAY')
)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY'))
) ;
 
-- Rename S_NQ_ACCT to S_NQ_ACCT2;
ALTER TABLE S_NQ_ACCT RENAME TO S_NQ_ACCT2;
 
-- insert into S_NQ_ACCT_TMP from S_NQ_ACCT2
INSERT INTO S_NQ_ACCT_TMP
SELECT * FROM S_NQ_ACCT2;
 
-- Drop S_NQ_ACCT2 + FK constraint;
DROP TABLE S_NQ_ACCT2 cascade constraints;
 
-- Rename S_NQ_ACCT_TMP to S_NQ_ACCT
ALTER TABLE S_NQ_ACCT_TMP RENAME TO S_NQ_ACCT;
 
-- Recreate FK constraintf
ALTER TABLE "S_NQ_ACCT" add CONSTRAINT "S_NQ_ACCT_PK" PRIMARY KEY ("ID");
ALTER TABLE "S_NQ_DB_ACCT" add constraint "FK_S_NQ_DB_ACCT" FOREIGN KEY ("LOGICAL_QUERY_ID") REFERENCES "S_NQ_ACCT" ("ID");
 
-- create index
CREATE INDEX "S_NQ_ACCT_M1" ON "S_NQ_ACCT"
(
"START_DT",
"START_HOUR_MIN",
"USER_NAME"
);
CREATE INDEX "S_NQ_ACCT_M2" ON "S_NQ_ACCT"
(
"START_HOUR_MIN",
"USER_NAME"
);
CREATE INDEX "S_NQ_ACCT_M3" ON "S_NQ_ACCT"
(
"USER_NAME"
);

And here is the one for S_NQ_DB_ACCT:

CREATE TABLE S_NQ_DB_ACCT_TMP AS
SELECT * FROM S_NQ_DB_ACCT;
DROP TABLE S_NQ_DB_ACCT;
CREATE TABLE "S_NQ_DB_ACCT"
(
"ID" NUMBER(10,0),
"LOGICAL_QUERY_ID" VARCHAR2(50 BYTE),
"QUERY_TEXT" VARCHAR2(1024 BYTE),
"QUERY_BLOB" CLOB,
"TIME_SEC" NUMBER(10,0),
"ROW_COUNT" NUMBER(20,0),
"START_TS" DATE,
"START_DT" DATE,
"START_HOUR_MIN" CHAR(5 BYTE),
"END_TS" DATE,
"END_DT" DATE,
"END_HOUR_MIN" CHAR(5 BYTE),
CONSTRAINT "FK_S_NQ_DB_ACCT" FOREIGN KEY ("LOGICAL_QUERY_ID") REFERENCES "S_NQ_ACCT" ("ID") ENABLE
)
PARTITION BY RANGE
(
START_DT
)
INTERVAL
(
NUMTODSINTERVAL(1,'DAY')
)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY'))
) ;
CREATE INDEX "S_NQ_DB_ACCT_I1" ON "S_NQ_DB_ACCT"
(
"LOGICAL_QUERY_ID"
);
INSERT INTO S_NQ_DB_ACCT
SELECT * FROM S_NQ_DB_ACCT_TMP;
DROP TABLE S_NQ_DB_ACCT_TMP;

In a later post I will put the the scripts to delete partitions older than a number of days.