Starting oracle admin web console

Go to the ORACLE_HOME/bin directory
Eg. /u01/app/oracle/product/11.1.0/db_1/bin

** To check the status enter ./emctl status dbconsole
** To start the enterprise db console enter ./emctl start dbconsole
** To stop the admin console enter ./emctl stop dbconsole

Unable to retrieve search results column is not indexed

When working in site studio and oracle ucm content server i got the following error when trying to select the assets drop down to insert a css or a image etc.

Unable to retrieve search results. Unable to retrieve search results. Unable to create result set for query 'SELECT IdcColl1.dID, dDocName, dDocTitle, dDocType, dRevisionID, dSecurityGroup, dDocAuthor, dDocAccount, dRevLabel, dFormat, dOriginalName, dExtension, dWebExtension, dInDate, dOutDate, dCreateDate, dPublishType, dRendition1, dRendition2, VaultFileSize, WebFileSize, URL, dFullTextFormat, dFullTextCharset, DocMeta.*FROM IdcColl1, DocMetaWHERE IdcColl1.dID=DocMeta.dID AND (((CONTAINS(dDocFullText,'test') > 0 ))) ORDER BY dInDate Desc'. ORA-20000: Oracle Text error:DRG-10599: column is not indexed

To fix this error need to Configure Site Studio Metadata and Zone Fields. To do this

1. Open the content server Administration page.

2. Click Zone Fields Configuration.

3. Move Web Sites and Exclude From Lists into Zone Text Fields column.

Then press update.

This should basically fix the issue above. Refer to the Site studio installation guide for more information.


Note:

According to the installation guide full text indexing is required by the ucm to run correctly even though you do not need full text indexing facility.

To do this execute the Database-Script [ucm-install-target]/database/oracle/admin/stellentrole.sql

Use the following command to enable the role to the user.
grant stellent_role to ;

Script for creating a user for content server is in [ucm-install-target]/database/oracle/admin/contentserveruser.sql. This script assigns required rights and roles to the content server user.

Enabling full text search

In addition to the above settings the following should be done to enable full text search.

If the database used is 10gR2 should run the following script
[ucm-install-target]/database/oracle/admin/batchsnippet.sql

Edit [ucm-install-target]/config/config.cfg and add the following:
SearchIndexerEngineName=DATABASE.FULLTEXT

Restart the content server.

Run the repository manager tool comes with the content server and rebuild the indexes.

This will basically enable full text search in content server. Please refer to the installation guild in the following location for further information.

http://download-west.oracle.com/docs/cd/E10316_01/cs/cs_doc_10/documentation/integrator/install_cserver_win_10en.pdf.

=====================

Error while getting remote MBeanServer

When creating a connection from the JDeveloper to the Oracle Application server you might get an error similar to the following.

Error while getting remote MBeanServer for url: ormi://176395.176395-web1.server.domain:12401/default

To fix this add the fully qualified application server name (which is 176395.176395-web1.server.domain in above case) to the hosts file in the client machine that runs JDeveloper which used to access the server.

The hosts file is located in the following locations on the client machine.

Linux - /etc/sysconfig/networking/profiles/default/hosts
Windows - C:\WINDOWS\system32\drivers\etc\hosts

The hosts file should look similar to the following after adding the entry.

127.0.0.1 localhost
83.138.171.4 176395.176395-web1.server.domain

Just adding the entry and saving the hosts file is enough and no need to restart anything for this to work.
======================================================

Oracle pfile and spfile

Oracle pfile is the parameter file which is used for initilazing the oracle database. This is usualy init.ora in the ${ORACLE_HOME}/dbs/ which is /u01/app/oracle/product/11.1.0/db_1/dbs in my case.

spfile is the binary counterpart of pfile which was introduced starting from Oracle 9i. spfile is used to store dynamic changes done via ALTER SYSTEM or ALTER SESSION statements.

On startup database first looks the spfile and if not found checks for pfile.

Creating spfile
spfile can be created as privilaged user by following command
SQL> create spfile from pfile;
The file is created in ${ORACLE_HOME}/dbs/ and named spfile{ORACLE_SID}.ora. The command will over write if a old file exist in the path and if its in use following error will throw.
ORA-32002: cannot create SPFILE already being used by the instance

Starting up with different spfile.
On startup the system checks the parameter file in following order.
spfile${ORACLE_SID}.ora file in ${ORACLE_HOME}/dbs/
spfile.ora in ${ORACLE_HOME}/dbs/
init${ORACLESID}.ora in ${ORACLE_HOME}/dbs/

To specify the parameter file on startup use the following command
SQL> startup pfile='/db2/ORACLE/SID/spfile/init.ora';

SCOPE and ALTER SYSTEM statements

Changes done by ALTER SYSTEM and ALTER SESSION statements are normally persistent in spfile. However this behaviour can be changed.

Specifying SCOPE=MEMORY and the changes are valid until the next restart and the spfile will not affected. To do this use the following commands.

SQL> SHOW PARAMETER timed_statistics
SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=memory;
SQL> SHOW PARAMETER timed_statistics

Specifying SCOPE=SPFILE and the changes will not affect the current running instance but will be visible after a database restart. For this use the following command.

SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=spfile;

Specifying SCOPE=BOTH will affect both the running instance and the spfile hence will be persistent. Following is the command to do this.

SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=both;

To check this parameter in both contexts memory and spfile use the following commands

SQL> SELECT name,value FROM v$parameter WHERE name='timed_statistics';
SQL> SELECT name,value FROM v$spparameter WHERE name='timed_statistics';

Check what paramter file (spfile or pfile) used in the database.

SQL> SELECT name,value FROM v$parameter WHERE name = 'spfile';

Export the spfile to pfile

SQL> create pfile from spfile;

OR can specify the file names as

SQL> create pfile='/db2/ORACLE/SID/spfile/init.ora' from spfile='/db2/ORACLE/SID/spfile/spfileorcl.ora'

===================

Oracle instance currently running

To retrieve the Oracle instance name, execute the following SQL statement:
select sys_context('USERENV','DB_NAME') as Instancefrom dual;

Following commands can be used to check the TNS Listener status

tnsping oracl (Where oracl is the instance name under concern

lsnrctl services

lsnrctl status

Increase sessions and processes of Oracle DB

If you use the oracle XE starter database and try to setup a bulky application like the oracle ucm content management system you will get the following error.

Unable to initialize the application. Error: Unable to initialize the system provider 'SystemDatabase'. Unable to create database connection for the database 'SystemDatabase' with connection string 'jdbc:oracle:thin:@localhost:1521:XE'. Please make sure that the connection string, user and password are correct. Listener refused the connection with the following error:ORA-12519, TNS:no appropriate service handler found

The default parameters of the starter database are not set to handle havy loads. To increase the parameters use the following commands.

Log into sqlplus by

sqlplus sys/password as sysdba (Replace password with administrator password).

Database Sessions

Increase the number of sessions to allow more users to access the database.
SQL> show parameters sessions
SQL> alter system set sessions=250 scope=spfile;

Database Processes

Increase the number of database processes which will allow more connections to the database.

SQL> show parameters processes
SQL> alter system set processes=200 scope=spfile;

Finally restart the database for the parameters to take effect.

This should fix the above error.