Category Archives: oracle

Oracle Forms and Reports 11g init startup script

After pulling my hair out searching the web and trying to cobble together shell commands to start and stop Oracle Forms 11g, I finally started playing with nodemanager to script the startup and shutdown. Nodemanager can start and stop all components, except the opmn stuff, and it blocks execution until the service is started. This way I don’t have to try to time stuff correctly using sleep commands and it just works a lot better.

Anyway, put this in a file /etc/init.d/forms and change the environment variables to match your environment.
Then run the following commands to install the script:

chmod 755 /etc/init.d/forms
chkconfig --add /etc/init.d/forms
#!/bin/bash
#
# oracle Init file for starting and stopping Oracle Forms and Reports 11g
#
# chkconfig: 35 95 30
# description: Oracle Forms and Reports 11g startup script

# Source function library
. /etc/rc.d/init.d/functions

# Set Environment
WLS_USER=weblogic
WLS_PW=SUPERSECRET
WLS_DOMAIN=MYDOMAIN
ORACLE_OWNER="oracle"
MIDDLEWARE_HOME="/opt/local/oracle/Middleware"
DOMAIN_HOME="${MIDDLEWARE_HOME}/user_projects/domains/${WLS_DOMAIN}"
WLS_HOME="${MIDDLEWARE_HOME}/wlserver_10.3"
OPMN_CMD="${MIDDLEWARE_HOME}/asinst_1/bin/opmnctl"

# Define and ensure boot.peroperties.
BOOT_PROPERTIES="username=${WLS_USER}\npassword=${WLS_PW}"
mkdir -p ${DOMAIN_HOME}/servers/AdminServer/data/nodemanager
mkdir -p ${DOMAIN_HOME}/servers/WLS_FORMS/data/nodemanager
mkdir -p ${DOMAIN_HOME}/servers/WLS_REPORTS/data/nodemanager
echo -e ${BOOT_PROPERTIES} > ${DOMAIN_HOME}/servers/AdminServer/data/nodemanager/boot.properties
echo -e ${BOOT_PROPERTIES} > ${DOMAIN_HOME}/servers/WLS_FORMS/data/nodemanager/boot.properties
echo -e ${BOOT_PROPERTIES} > ${DOMAIN_HOME}/servers/WLS_REPORTS/data/nodemanager/boot.properties
chown -R oracle.oinstall ${DOMAIN_HOME}/servers/AdminServer/data/nodemanager
chown -R oracle.oinstall ${DOMAIN_HOME}/servers/WLS_FORMS/data/nodemanager
chown -R oracle.oinstall ${DOMAIN_HOME}/servers/WLS_REPORTS/data/nodemanager

# Define NodeManager startup script
START_CMD=$(cat <<EOF
startNodeManager(verbose='true', NodeManagerHome='${WLS_HOME}/common/nodemanager')
nmConnect('${WLS_USER}','${WLS_PW}','localhost','5556','${WLS_DOMAIN}')
nmStart('AdminServer')
arg=\"Arguments=\\\\\" -Dweblogic.management.server=localhost:7001\\\\\"\"
prps=makePropertiesObject(arg)
nmStart('WLS_FORMS',props=prps)
nmStart('WLS_REPORTS',props=prps)
exit()
EOF
)

# Define NodeManager shutdown script
STOP_CMD=$(cat <<EOF
nmConnect('${WLS_USER}','${WLS_PW}','localhost','5556','${WLS_DOMAIN}')
nmKill('WLS_REPORTS')
nmKill('WLS_FORMS')
nmKill('AdminServer')
stopNodeManager()
exit()
EOF
)

start() {
  echo "Starting WLS:"
  su - $ORACLE_OWNER -c "echo \"${START_CMD}\" | ${WLS_HOME}/common/bin/wlst.sh | grep --line-buffered -i 'Error\|Success' | grep -v INFO"
  su - $ORACLE_OWNER -c "${OPMN_CMD} startall"
  echo "OK"
}

stop() {
  echo "Stopping WLS:"
  su - $ORACLE_OWNER -c "${OPMN_CMD} stopall"
  su - $ORACLE_OWNER -c "echo \"${STOP_CMD}\" | ${WLS_HOME}/common/bin/wlst.sh | grep --line-buffered -i 'Error\|Success' | grep -v INFO"
  echo "OK"
}

restart() {
  stop
  start
}


case "$1" in
  start)
    start
  ;;
  stop)
    stop
  ;;
  restart)
    restart
  ;;
  *)
    echo $"Usage: $0 {start|stop|restart}"
esac

Move Oracle data files

The procedure below will allow you to move Oracle data and redo files.

1. It is a good idea to make a backup of the directory you are moving just in case something goes horribly wrong. 🙂
# cp -rp {data_dir} {backup_dir}

2. log in as the oracle user
# su -l oracle

3. move the database files
# mv {data_dir} {new_data_dir}

4. Set SID, log into database.
# export ORACLE_SID={ORACLE_SID}
# sqlplus /nolog
SQL> connect /as sysdba;

5. Create pfile from spfile
SQL> create pfile from spfile;

6. In another terminal, modify the newly created pfile. It is usually called init{SID}.ora and can be in different directories. Look in udump, dbs, and other directories until you find the newly created pfile. Check the date to make sure it is the one you are looking for. Now, fix the directories for the control_files variable.

7. Go back to the original terminal and create spfile from pfile;
SQL> create spfile from pfile;

8. Mount database.
SQL> startup mount;

9. Alter the database to look in the new location for the data files and redo logs. Below is an example. (Do this for all data and redo logs.)
SQL> alter database rename file ‘/home/oracle/oradata/{SID}/sysaux01.dbf’ to ‘/usr/local/app/oracle/oradata/{SID}/sysaux01.dbf’
SQL> alter database rename file ‘/home/oracle/oradata/{SID}/redo01.log’ to ‘/usr/local/app/oracle/oradata/{SID}/redo01.log’

10. Open database.
SQL> alter database open;

Now it should hopefully come up properly. You man want to shut it down and bring it up once more to make sure everything is working properly.

Oracle Bug : exp-00003 for LOB tables

Disclaimer: This is plagiarized! See: http://www.anysql.net/en/oracle/oracle_bug_exp00003.html

When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error “EXP-00003 : no storage definition found for segment …..”, actually this is an Oracle bug, you could temporary get it resolved by replace a view “exu9tne”, as following:

Before exporting, run the following SQL under sys:
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/

After exporting, run the following to restore the view definition according to Metalink Notes.
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
/