Changing Blackboard 9.1 SP13 Oracle Passwords

As of Blackboard Learn 9.1 SP13 CP6


Change SYS password
SQL> alter user sys identified by newsyspassword;


Change SYSTEM password

SQL>alter user system identified by newsystempassword;

bbconfig.database.server.systemuserpassword=newsystempassword
bbconfig.cs.db.systemuser.pass=newsystempassword



Change BBLEARN_REPORT Password

SQL>alter user BBLEARN_REPORT identified by newreportpassword;
antargs.default.vi.report.user.password=newreportpassword


Change BBLEARN_STATS Password
SQL>alter user BBLEARN_STATS identified by newstatspassword;
SQL>update BBLEARN_ADMIN.bb_instance set STAT_DB_PASS='newstatspassword';

antargs.default.vi.stats.db.password=newstatspassword


Change BBLEARN & BBLEARN_ADMIN password must have the SAME password

SQL>alter user BBLEARN_ADMIN identified by adminpassword;
SQL>update BBLEARN_ADMIN.bb_instance set db_pass='adminpassword';

bbconfig.database.admin.password=adminpassword


SQL>alter user BBLEARN identified by adminpassword;
antargs.default.vi.db.password=adminpassword


CMS_USER && BBLEARN_CMS_DOC  must have the SAME Password

SQL>alter user BBLEARN_CMS identified by newcmspassword;
bbconfig.cs.db.cms-user.pass=newcmspassword


SQL>alter user BBLEARN_CMS_DOC identified by newcmspassword;
SQL>update BBLEARN_CMS.xy_file_systems set db_password='newcmspassword';

PushConfig Issues & ORA-3136

PushConfig fails on BB9.1 and Oracle 10.2.0.4 with oracle alert_logs error message
"WARNING: inbound connection timed out (ORA-3136)

Solution


Add this line to the /app/oracle/product/10.2.0/db_1/network/admin/listener.ora
INBOUND_CONNECT_TIMEOUT_LISTENER = 0

Do the same in /app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT =0

What's in analyze_my.bbtabs (BB9.1) ?

select OWNER, NAME from dba_source where name like '%analy%'


SQL> desc bb_bb60.analyze_my
PROCEDURE ANALYZE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABNAME VARCHAR2 IN
PROCEDURE BBTABS


Checking under the hood

SQL> select text from dba_source where name = 'ANALYZE_MY' and owner = 'BB_BB60';


TEXT
--------------------------------------------------------------------------------
PACKAGE analyze_my AS

PROCEDURE bbtabs;
PROCEDURE analyze_table(tabname VARCHAR2);

END analyze_my;
PACKAGE BODY analyze_my AS

PROCEDURE parse_n_execute_ddl(strng VARCHAR2) IS
c1 NUMBER;
r1 NUMBER;

TEXT
--------------------------------------------------------------------------------
BEGIN
c1 := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c1,strng,DBMS_SQL.NATIVE);
r1 := DBMS_SQL.EXECUTE(c1);
DBMS_SQL.CLOSE_CURSOR(c1);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(c1);
END parse_n_execute_ddl;

PROCEDURE bbtabs

TEXT
--------------------------------------------------------------------------------
IS
db_block_size_v NUMBER;
db_block_buffers_v NUMBER;
cache_size_v NUMBER;
strng VARCHAR2(100);
p_large_thresh NUMBER := 10000000;
p_degree_large NUMBER := null;
p_degree NUMBER := null;
p_est_pct_large NUMBER := 20;
p_est_pct NUMBER := null;


TEXT
--------------------------------------------------------------------------------
-- Cursor to list all tables that appear to have no rows and which haven't b
een analyzed in the past day

CURSOR c_zerotabs IS SELECT table_name FROM user_tables WHERE num_rows = 0 A
ND last_analyzed < (SYSDATE -1); -- Cursor to look for large tables exceeding the table monitoring first CURSOR c_large_tab (cp_thresh NUMBER) IS SELECT m.table_name FROM user_tab_modifications m, user_tables t WHERE TEXT -------------------------------------------------------------------------------- t.table_name = m.table_name AND t.num_rows > 0
AND (m.inserts + m.updates + m.deletes)/t.num_rows > 0.095
AND t.num_rows > cp_thresh;

BEGIN
-- Proactively gather stats for large tables that are getting close, so that th
ey can be estimated

FOR r_large_tab IN c_large_tab (p_large_thresh) LOOP
dbms_stats.gather_table_stats(

TEXT
--------------------------------------------------------------------------------
ownname => NULL,
tabname => r_large_tab.table_name,
estimate_percent => p_est_pct_large,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => p_degree_large,
cascade => TRUE
);
END LOOP;

-- Gather stats where none exist

TEXT
--------------------------------------------------------------------------------
dbms_stats.gather_schema_stats(
ownname => NULL,
estimate_percent => p_est_pct,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => p_degree,
options => 'GATHER EMPTY',
cascade => TRUE
);

-- Gather stats for those that are stale (i.e. have seen signficant change)

TEXT
--------------------------------------------------------------------------------
dbms_stats.gather_schema_stats(
ownname => NULL,
estimate_percent => p_est_pct,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => p_degree,
options => 'GATHER STALE',
cascade => TRUE
);

-- Gather stats for those tables which stats show are empty, since this is s

TEXT
--------------------------------------------------------------------------------
ometimes wrong and can lead to problems if it is

FOR r_zerotabs IN c_zerotabs LOOP
dbms_stats.gather_table_stats(
ownname => NULL,
tabname => r_zerotabs.table_name,
estimate_percent => NULL,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => NULL,
cascade => TRUE

TEXT
--------------------------------------------------------------------------------
);
END LOOP;

BEGIN
SELECT value
INTO db_block_size_v
FROM v$parameter
WHERE name = 'db_block_size';
SELECT value
INTO db_block_buffers_v
FROM v$parameter

TEXT
--------------------------------------------------------------------------------
WHERE name = 'db_block_buffers';
cache_size_v := (db_block_size_v*db_block_buffers_v)/10;
EXCEPTION
WHEN others THEN
db_block_size_v := 8192;
cache_size_v := 999999999;
END;

FOR c2 IN (SELECT table_name
FROM user_tables
WHERE cache = ' N'

TEXT
--------------------------------------------------------------------------------
AND NVL(blocks,999999999)*db_block_size_v <= cache_size_v) LOOP BEGIN strng := 'ALTER TABLE '||c2.table_name||' CACHE'; parse_n_execute_ddl(strng); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; TEXT -------------------------------------------------------------------------------- FOR c3 IN (SELECT table_name FROM user_tables WHERE cache = ' Y' AND NVL(blocks,0)*db_block_size_v > cache_size_v)
LOOP
BEGIN
strng := 'ALTER TABLE '||c3.table_name||' NOCACHE';
parse_n_execute_ddl(strng);
EXCEPTION
WHEN OTHERS THEN
NULL;

TEXT
--------------------------------------------------------------------------------
END;
END LOOP;

END bbtabs;

PROCEDURE analyze_table(tabname varchar2) IS
strng VARCHAR2(100);
BEGIN

BEGIN
strng := 'dbms_stats.gather_table_stats(tabname=>'''||tabname||''',

TEXT
--------------------------------------------------------------------------------
method_opt=>''for all indexed columns size auto'',estimate_percent=>100)';

parse_n_execute_ddl(strng);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END analyze_table;

END analyze_my;

154 rows selected.

Changing Oracle User Passwords in Blackboard 8

if you changed the oracle SYS password, the following must be changed:
===>bbconfig.database.bbadmin.machine.systemuserpassword

if you changed the oracle SYSTEM password, the following must be changed:
===>bbconfig.cs.db.systemuser.pass


If you change Oracle BB_BB60 password, remember to update the bbadmin.bb_instance table.

===>alter user BB_BB60 identified by ;
===>update BBADMIN.bb_instance set db_pass='';
===> antargs.default.vi.db.password=


If you change the BBADMIN user password
==> bbconfig.database.bbadmin.db.password=



if you change BB_BB60_STATS
==>antargs.default.vi.stats.db.password
===>update BBADMIN.bb_instance set stat_db_pass='';



if you change BB_BB60_REPORT
==>antargs.default.vi.report.user.password



SQL> alter user cms identified by newcontent123; --all six database users for Content System
SQL> alter user cms_files_users identified by newcontent123; --must use the same password!!!
SQL> alter user cms_files_courses identified by newcontent123;
SQL> alter user cms_files_orgs identified by newcontent123;
SQL> alter user cms_files_inst identified by newcontent123;
SQL> alter user cms_files_library identified by newcontent123;
SQL> update cms.xy_file_systems set db_password='newcontent123';
SQL> commit;

==>bbconfig.cs.db.cms-user.pass=newcontent123

Changing Oracle User Passwords in Blackboard 9.1

Here's the list of usernames used by Blackboard in Oracle

BBLEARN_REPORT
BBLEARN_CMS
BBLEARN_CMS_DOC
BBLEARN_STATS
BBLEARN
BBLEARN_ADMIN
SYSTEM
SYS

if you changed the oracle SYS password, the following must be changed:
====>bbconfig.database.server.systemuserpassword


if you changed the oracle SYSTEM password, change the following:
====>bbconfig.cs.db.systemuser.pass=



if you change the Oracle BBLEARN_ADMIN, change the following:
====>bbconfig.database.admin.password=


if you change the Oracle BBLEARN_REPORT, change the following:
====>antargs.default.vi.report.user.password=



If you change Oracle BBLEARN password, remember to update the bbadmin.bb_instance table.

===>alter user BBLEARN identified by ;
===>update BBLEARN_ADMIN.bb_instance set db_pass='';
===> antargs.default.vi.db.password=



If you change Oracle BBLEARN_STATS password, remember to update the bbadmin.bb_instance table.

===>alter user bb_bb60_stats identified by ;
===> update BBLEARN_ADMIN.bb_instance set stat_db_pass='';
===>antargs.default.vi.stats.db.password



/* Change CMS password does not work. currently encountered the following error */

Caused by: com.xythos.common.InternalException: JDBCConnectionPool:190 - (ORA-01017: invalid username/password; logon denied

) InvocationTargetException: check database configuration settings for store 'XYTHOS_POOL', DB username 'BBLEARN_cms', at URL jdbc:oracle:thin:@bb.bee-net.com:1521:bb60

To change the CMS and CMS_DOC passwords

SQL> SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE '%CMS%';

USERNAME
------------------------------
BBLEARN_CMS_DOC
BBLEARN_CMS

SQL> alter user bblearn_cms identified by newpassword;

User altered.

SQL> alter user bblearn_cms_doc identified by newpassword;

User altered.

SQL> update bblearn_cms.xy_file_systems set db_password='newpassword';

1 row updated.

SQL> commit;

Commit complete.


Edit the bb-config.properties file. Change the bbconfig.cs.db.cms-user.pass=newpassword
Run PushConfigUpdates.sh

Changing Database SYS and SYSTEM password

Go here

Blackboard - Avoiding java.sql.SQLException: ORA-01031: insufficient privileges Issue

sqlplus "/as sysdba"

SQL> @$ORACLE_HOME/rdbms/admin/rstrconn.sql
SQL> GRANT create session, create table TO CONNECT;
SQL> commit;
SQL> grant sysoper to system;
SQL> grant sysdba to sys;
SQL> commit;

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SYSTEM                         FALSE TRUE

SQL> show parameter password;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

Also check LOCKED accounts.

SQL> SELECT username, account_status FROM dba_users;
SQL> ALTER USER the_username ACCOUNT UNLOCK;


sqlplus “sys/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=databasehostname)(PORT=1521))(CONNECT_DATA=(SID=$ORACLE_SID))) AS SYSDBA”

sqlplus
“system/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=databasehostname)(PORT=1521))(CONNECT_DATA=(SID=$ORACLE_SID))) AS SYSOPER”

Blackboard - Testing SQL Connection from application node to database

sqlplus “sys/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=databasehostname)(PORT=1521))(CONNECT_DATA=(SID=$ORACLE_SID))) AS SYSDBA”

sqlplus “system/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=databasehostname)(PORT=1521))(CONNECT_DATA=(SID=$ORACLE_SID))) AS SYSOPER”

Blackboard - Analyze Schema

**Last Analyzed**

sqlplus "/as sysdba";

sqlplus> select OWNER,TABLE_NAME,last_analyzed from dba_tables where tablespace_name = 'BB_BB60_DATA';

sqlplus> select OWNER,TABLE_NAME,last_analyzed from dba_tables where tablespace_name = 'BBADMIN_DATA';

sqlplus> select OWNER,TABLE_NAME,last_analyzed from dba_tables where tablespace_name = 'BB_BB60_STATS_DATA';


**List Jobs**

SELECT job, schema_user, next_date, broken, what from dba_jobs;


**Remove Job**

execute sys.dbms_ijob.remove(n);

**Create New Analyzed_Schema Jobs**

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''BB_BB60_STATS'',

cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''SYS'',

cascade=>TRUE);',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'' BBADMIN'',

cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/

DECLARE

JobNo dba_jobs.job%TYPE;

BEGIN

DBMS_JOB.SUBMIT(JobNo,'DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>''BB_BB60'',

cascade=>TRUE, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'');',

trunc(sysdate) + 7/24 + 8 - to_char(sysdate,'D'), 'trunc(sysdate) + 7 + 7/24');

COMMIT;

END;

/


or simply run the following

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'BB_BB60_STATS', cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'BB_BB60', cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'BBADMIN', cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');