Posts Tagged ‘Oracle’

sql>connect / as sysdba
sql>shutdown immediate;
sql>startup migrate;
sql> @$ORACLE_HOME/rdbms/admin/utlirp.sql
sql>shutdown immediate;
sql> startup
sql>
sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql
sql> connect system/password

Happy times…

Advertisements

1. Dump a trace of the controlfile.
Use SQL*Plus (or Server Manager) and enter:
SQL> alter database backup controlfile to trace ;
2. Modify the created trace file (located in the user_dump_dest directory):
a) remove from the first line to the line before “STARTUP NOMOUNT”
b) remove line “ALTER DATABASE OPEN” to the end of the file
c) change value of maxdatafiles, maxlogfiles and other parameters as desired
d) rename the file to something .sql (such as cntl.sql)
3. Shutdown the database.
4. Backup the database.
5. Run Server Manager/SQLPLUS and do the following:
SVRMGR> connect internal or connect /as sysdba
SVRMGR> @cntl.sql (The SQL script that you created in step 2) or SQLPLUS>@cntl.sql
SVRMGR> alter database open noresetlogs ; or SQLPLUS> alter database open noresetlogs

SQLPLUS if you are using 9i SVRMGRL if you are using 8i or below.

Also Please read the following article from metalink…

Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 14-JAN-2005

PURPOSE
This article describes how you can recreate your controlfile.

SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.

WARNING:
——–

You should only need to recreate your control file under very special
circumstances:

– All current copies of the control file have been lost or are corrupted.

– You need to change a “hard” database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
MAXLOGHISTORY, etc.

– You are restoring a backup in which the control file is corrupted or
missing.

– Oracle Customer Support advises you to do so.

– If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.

We can install different oracle versions on same machine like Oracle 8i,9i or 10g. Also, we can create multiple database/instance on a single machine but remember to name them differently, for  example: db1,db2,db3 and so on. When name them like that, we avoid conflicting installing them on our single machine.

Suppose we planned to install oracle 8i,9i or 10g on same machine then we need to set oracle enviourment variable to point LATEST VERSION. Like this one:

ORACLE_SID=SIDNAME
ORACLE_HOME=10ORACLECHOME
PATH=$ORACHOME/bin(10G)
TNS_ADMIN=$ORACLE_HOME/network/admin(10g)
After that, we can configure LISTENER.ORA we use for three different version. There is two option for this.
First one: create separate listener.ora file for each version like

  1. listener with default port(1521)
  2. listener1 with separate port
  3. listener2 with separate port

    or

Second one: use only one listener.ora file for all three oracle homes. To do so, please follow following instructions:
1. Stop listener services of Oracle 8i,9i database
2. create listener.ora file for 10g. and edit 10g listener.ora file then do configure for oracle 8i or 9i database like below:

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
(SID_LIST=

(SID_DESC=
(GLOBAL_DBNAME=Ora8i)
(ORACLE_HOME=/oracle8i)
(SID_NAME=Ora8i))

(SID_DESC=
(GLOBAL_DBNAME=Ora9i)
(ORACLE_HOME=/oracle9i)
(SID_NAME=Ora9i))

(SID_DESC=
(GLOBAL_DBNAME=Ora10g)
(ORACLE_HOME=/oracle10g)
(SID_NAME=Ora10g))

(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle10g)
(PROGRAM=extproc)))

So please enjoy,

Minh Tran

1. First step is to download the Oracle Database 10g Client Release 2 (10.2.0.1.0) for Linux x86 zip file from www.oracle.com and extract it somewhere.

2. Unzip 10201_client_linux32.zip to a folder

3. Open terminal and run

./runInstaller -ignoreSysPrereqs

4. Following instructions on OUI to install your software. Note that the error appear when you go about 88% installing about linking files can safely be ignored by clicking Continue. If you face error when progress only reach 87% or early, you should update latest patches for Ubuntu by running update-manager then re-run the installation process.

5. When finishing the installation, you may want to configure your database connections. If you have an existing tnsnames.ora you can use, you can just copy that over to $ORACLE_HOME/network/admin/ instead, otherwise you can use this wizard to create a new connection.

6. Exit from installation process when all the copy done.

7. Once you’ve finished creating your connections there’s a script you need to run as root.

sudo {ORACLE_HOME}/root.sh
Password:

Just agree with all the defaults presented and after the script has finished, we’re done.

8. Open terminal and enter following lines:

sudo gedit

after that, open the .bashrc file and append these lines:

export ORACLE_HOME=your_path_to_oracle_home_client
export PATH={your_path_to_oracle_home_client}/bin:$PATH

9. Restart the machine to apply the configuration.

10. Now you can test by open terminal and enter: sqlplus. If you want some gui-tools, create a launcher with launch parameter as: oemapp console

11. and enjoy.

Minh Tran

Wanna “clear” the temp default tablespace, huh? Answer is NO WAY!

Luckily you can create a new TEMPORARY TABLESPACE and set it as default.

Then  Drop the old one…

increase size for the new TEMP TABLESPACE.

And there you go…

Minh-Tran

When developing JSF pages using Oracle ADF Faces 10.1.3.3, I encountered the error that is reported at the top of the page as: JBO-35007 indicated that the row currency has changed since last visit or something. This was so annoy that it is repeatedly occurred everytimes I hit the back button. Sometimes it disappear when I press Refresh.
Googling around, I found two working solutions for the error:

1.Go to page definition of jsp or jspx by right click on page.
2.Select Iterator of page by using structure window or definition page
3.Go to the Inspector window and change the StateValidation property as false.

“And as can be seen from the JDev doco note, the row currency token mechanism can be turned off per page through the pageDef for your page, setting the EnableTokenValidation to false. (Be warned you should not flippantly turn this off for pages, or blanket turn it off for all pages, as the mechanism is an important one)

So enjoy your programming…

Minh-Tran

Source: OTN JDeveloper Forum

All

Just successfully deployed a JSF/ADF-BC4J app developed in Jdev 10.1.3.3 to Tomcat-6.0.14 and MySQL 5.0

Thought I would share since i could not find anything out there that gave me the steps for this configuration combo.

Assumptions:
1. install MySQL 5.0 database with some tables.
2. install Tomcat-6.0.14 (http://archive.apache.org/dist/tomcat/tomcat-6/v6.0.14/bin/) probably any 6.x build would be same
3. Application Modules developed in Jdev are currently configured to run on MySQL (SQL92 flavor / Java type mappings)
4. This is for ADF-BC4J only. TopLink would require add’l steps, though I assume the only difference would be add’l jar files

I did all development in Jdev using the internal OC4J and a local MySQL before deploying to Tomcat.

Steps:
1. copy following jars from <JDEV_INSTALL> to <TOMCAT_HOME>\lib\
(Note that ‘Tools -> ADF Runtime Installer’ does not work for Tomcat 6.x, so this has to be manual)

\BC4J\lib\adfcm.jar
\BC4J\lib\adfm.jar
\BC4J\lib\adfmweb.jar
\BC4J\lib\adfshare.jar
\BC4J\lib\bc4jct.jar
\BC4J\lib\bc4jctejb.jar
\BC4J\lib\bc4jdomorcl.jar
\BC4J\lib\bc4jimdomains.jar
\BC4J\lib\bc4jmt.jar
\BC4J\lib\bc4jmtejb.jar
\BC4J\lib\collections.jar
\BC4J\jlib\bc4jdomgnrc.jar
\BC4J\jlib\adfmtl.jar
\BC4J\jlib\adfui.jar
\jlib\commons-cli-1.0.jar
\jlib\xmlef.jar
\jlib\share.jar
\mds\lib\mdsrt.jar
\mds\lib\concurrent.jar
\diagnostics\lib\ojdl.jar
\jlib\jdev-cm.jar
\jlib\commons-el.jar
\jlib\oracle-el.jar
\jlib\jsp-el-api.jar
\toplink\jlib\toplink.jar
\toplink\jlib\antlr.jar
\BC4J\lib\adfbinding.jar
\lib\xmlparserv2.jar
\lib\xml.jar
\lib\xsqlserializers.jar
\lib\xsu12.jar
\ord\jlib\ordhttp.jar
\ord\jlib\ordim.jar
\sqlj\lib\runtime12.jar
\sqlj\lib\translator.jar
\jdbc\lib\ojdbc14.jar
\jdbc\lib\ojdbc14dms.jar
\lib\dms.jar
\javacache\lib\cache.jar
\BC4J\jlib\dc-adapters.jar
\BC4J\jlib\adf-connections.jar
\webservices\lib\wsdl.jar
\webservices\lib\orajaxr.jar
\webservices\lib\orawsrm.jar
\webservices\lib\wsclient.jar
\webservices\lib\orasaaj.jar
\webservices\lib\xsdlib.jar
\webservices\lib\mdds.jar
\webservices\lib\wssecurity.jar
\webservices\lib\orawsdl.jar
\jlib\osdt_core.jar
\jlib\osdt_cert.jar
\jlib\osdt_xmlsec.jar
\jlib\osdt_wss.jar
\jlib\osdt_saml.jar
\jlib\ojpse.jar
\jlib\oraclepki.jar
\j2ee\home\jazncore.jar
\j2ee\home\lib\http_client.jar

2. copy <JDEV_INSTALL>\BC4J\redist\webapp.war to <TOMCAT_HOME>\webapps\webapp.war

3. in your application’s webroot, create a META-INF folder containing a single file, named ‘context.xml’, with the following (enter your appropriate values as usual):

<?xml version=”1.0″ encoding=”UTF-8″?>
<Context
antiJARLocking=”true”
antiResourceLocking=”true”
reloadable=”true”>
<Resource
auth=”Container”
name=”jdbc/MySqlDS”
type=”com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource”
factory=”com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory”
driverClassName=”com.mysql.jdbc.Driver”
maxIdle=”2″
maxWait=”5000″
url=”jdbc:mysql://<HOSTNAME>:<PORT>/<DATABASE>?&ultraDevHack=true&capitalizeTypeNames=true&pedantic=true”
user=”<USERNAME>”
password=”<PASSWORD>”
databaseName=”<DATABASE>”
port=”<PORT>”
maxActive=”10″/>
<Manager
className=”org.apache.catalina.session.PersistentManager”
saveOnRestart=”false”/>
</Context>

4. in all Application Modules, set the Configuration to use a ‘JDBC Datasource’ with the name set to the named datasource in the context.xml created above, e.g.: ‘java:comp/env/jdbc/MySqlDS’

5. create a deployment profile with ‘Platform -> target connection’ set to the Tomcat instance (need to create an entry in ‘Connections -> Application Server’ for it to show up) make sure to include the correct mysql-connector.jar in the ‘WEB-INF/lib’ contributors.
Right-click -> Deploy to your tomcat

6. Make sure MySQL is on and fire up Tomcat, and youre set.

Hope it helps
Sami