Just a quick code snippet, that couldn’t find anywhere on the net as an example and my colleague Aggelos offered to me 🙂
Just a quick code snippet, that couldn’t find anywhere on the net as an example and my colleague Aggelos offered to me 🙂
In this tutorial I will explain how to use the very convenient MySQL query browser to connect and work to your remote MySQL server, where you do not allow remote TCP connections for security reasons (and you do very well!)
Many users use web gui tools like phpMyAdmin, but they have limitations and are some times frustrating to use.
I suppose you work in Windows. If you work in Linux, this tutorial will help you too and you can ommit the part of the Cygwin installation (see below).
To accomplish the goals of this tutorial we will use 2 great free tools:
Get them now!
I also suppose that you have ssh access to your database server. If not (eg you are on a shared hosting environment), you can not proceed with this tutorial.
After the downloads get complete, install the tools. The installation is pretty straightforward and is out of the scope of this tutorial. One thing you have to be carefull about though, is to install the package openssh in Cygwin, as this will do the most tricky and clever part of the dirty job for us 🙂
Now run Cygwin simply by double clicking the shortcut that you may have created on your desktop or by using the cygwin.bat at the directory you installed Cygwin. A unix like command line appears. In there you must enter the “magic” command:
ssh -N -f -L 4783:127.0.0.1:3306 username@servername
where username the ssh user account to connect to your server and servername the IP or URL of your server. This supposes that you ssh to your server at the default port. If you connect at another port, you should user the parameter -p port as well.
But what does this command do? It creates an ssh tunel from your pc to your server and each call that arrives at your pc at the port 4783 (this is random you can choose whatever you like and is free on your pc), is forwarded to port 3306 (the MySQL service port) at your server! If you have configured your MySQL server to listen to another port, you should change the command accordingly!
Now, things are easy, open MySQL query browser and create a new connection. As servername use localhost (!) and as port the one that you defined above (4783). Insert the username and password of your remote db server though!
You will get a warning that you have not specified a default schema (if you havent). Just ignore it!
If you have done everything ok, you should be browsing your remote MySQL database as if it where on your localhost!
These are some instructions to install a very good forum software for Oracle Application Server Portal. The deveopers website has good documentation on it, but I am also writing down here, my experience installing it.
Download the software from http://www.sylohe.com/forum/
Unzip it to a directory, preferably straight to the root (c:\ )
Open an sqlplus window
Connect as system user
First you will need to create the tablespaces for this application.
You will need 3 tablespaces :
A data tablespace.
A temporary tablespace.
So if you don’t want to use existing tablespaces execute the following in sqlplus:
create tablespace forum_usr datafile ‘C:\ORADATA\ORCL\forum_usr.dbf’ size 50m autoextend on next 10m maxsize 100m;
create temporary tablespace forum_tmp tempfile ‘C:\ORADATA\ORCL\forum_tmp.dbf’ size 32m autoextend on next 32m maxsize 2048m;
create tablespace forum_ind datafile ‘C:\ORADATA\ORCL\forum_ind.dbf’ size 50m autoextend on next 10m maxsize 100m;
Now we need to run the install script. We execute @C:\forum0.6.5\installer\FORUM_INSTALL.sql according to the path we unzipped the forum files.
We need to answer to the installer questions as following:
folder for the log file : c:\forumlog\ (watch the trailing \ !)
portal schema name and password: PORTAL (or whatever applies)
db TNS connect string: (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))(CONNECT_DATA =(SID = ORCL))) change hostname and SID accordingly
version newer that 9somthing: TRUE (if it is true ofcourse:)
forum schema and password : forum_app and whatever
default tablespace for forum user: forum_usr (the one we created at the beginning or whatever else)
temporary tablespace for forum user: forum_tmp (the one we created at the beginning or whatever else)
tablespace for forum index: forum_ind (the one we created at the beginning or whatever else)
website url: http://www.something.com (without trailing / )
the DAD (Database Access Descriptor) of our portal : /pls/portal (without trailing / or whatever that applies to your installation)
SSO url: it is something like http://10.0.2.15:7777/pls/orasso
images url: /images/forum (without trailing slash)
path to access the forum : /forum (so to access your forum you will type http://www.something/forums afterwards)
forum title: My Community (this will appear on the browsers titlebar among other places)
administrator group: dba
lagnuage for installation: us (or your preferred one)
If everything goes ok, you will see the installation process finishing normally. We have to go now to the log file (it should be to the path we specified, somewhere like c:\forumlog\forum_installation.log ) and find at the last section a lot of entries that look like:
#– Forum Rewrite Part
RewriteRule ^/forums/?$ http://10.0.2.15/portal/pls/portal/pls/portal/FORUM_APP.forum_display.display_forum [L,P]
RewriteRule ^/forums/fastlab/?$ http://10.0.2.15/portal/pls/portal/pls/portal/FORUM_APP.forum_display.fastlab [L,P]
RewriteRule ^/forums/viewforum_([0-9]+)_([0-9]+).html$ http://10.0.2.15/portal/pls/portal/pls/portal/FORUM_APP.forum_display.display_parent?p_forum_id=$1&p_page=$2 [L,P,QSA]
Copy this and go to the application Server managment web interface. Open Http Server and go to Administration > Adnanced Server Properties. Click on httpd.conf and paste the above to the end of the file. Restart the Httpd Server.
Copy the images folder from the unzipped forum files to <ORACLE_HOME>/portal/images/forum folder (eg. C:\OraHome_Portal\portal\images\forum
Now you should be able to see your forums by typing http://www.something/forums
The administration login is portal ? your portal user pass exactly as at the Portal (SSO is working :))
Try making a new forum and make a new post on it. If everything looks fine,OK. If you get an error like this ?[Error in forum_package.post_item : -20001 ORA-20001: Errors ORA-20000: Oracle Text error: DRG-10502: index TOPIC_TITRE_IDX does not existORA-20000: Oracle Text error: DRG-10502: index TOPIC_TEXTE_IDX does not existORA-20000: Oracle Text error: DRG-10502: index TOPIC_FILE_IDX does not exist] ?after making the post, some indexes where not created and you need to create them manually:
Open an sqlplus window and login as user forum_app.
CREATE INDEX SMILEY_ALIAS_IDX ON T_SMILEYS(SMILEY_ALIAS) tablespace forum_ind;
CREATE INDEX TOPIC_FILE_IDX ON T_FORUM_UPLOADED_ITEMS(FILE_UP) indextype IS ctxsys.CONTEXT PARAMETERS(”);
CREATE INDEX TOPIC_TEXTE_IDX ON T_ITEMS_TEXT(TEXTE) indextype IS ctxsys.CONTEXT PARAMETERS(‘filter ctxsys.null_filter’);
CREATE INDEX TOPIC_TITRE_IDX ON T_ITEMS(TITRE) indextype IS ctxsys.CONTEXT PARAMETERS(‘filter ctxsys.null_filter’);
CREATE INDEX T_PM_MESSAGES_TEXTE_IDX1 ON T_PM_MESSAGES_TEXTE(MESSAGE_ID) tablespace forum_ind;
(Thanks to Helyos for the last tip-fix!)
Everything should be working now!
Ένα howto που το γράφω στα Αγγλικά, και θα με βοηθούσε και εμένα αν το είχα βρει έτοιμο!
Πώς να εγκαταστήσετε το Oracle Application Server Portal (10.1.4) στον Windows 2003 Server
1.Download all Oracle Application Server 10.1.2 Cds, corresponding to your OS (http://www.oracle.com/technology/software/products/ias/htdocs/101202.html)
2.Download Portal Upgrade (10.1.4) CD for the same page
3.Unzip all files
4.If you prefer not to burn the Cds, copy them to a directory not containing any spaces (preferrably straight to C:\)
5.First we need to install Oracle Application Server Infrasrtucture. Go to Disk1 and run Setup.exe. Oracle Universal Installer pops up, if all checks are ok. Click next.
6.Specify File Locations Screen. Leave source as is. On the target section, put some descriptive names, eg destination name oracleasInfra and destination path C:\OraHome_Infra
7.On the next screen, ?select a Product to install?, choose option 2: Oracle Application Server Infrastructure?
8.The next screen is ?Select Installation Type?. Choose first option ?Identity Managment and Metadata Repository?.
9.On the next screen confirm that you have Administrator Privileges
10.Next screen is ?Select Configuration Options?. Leave it as is (all selected except the last two).
11.On the specify Port Configuration screen, leave it to Automatic (unless you know what you are doing)
12.On the specifiy namespace in Internet Direcory leave it to suggested (unless you know what you are doing)
13.The next screen lets you configure the Oracle Database 10g that will be installed, as Oracle Infrastructure needs it. Choose a name and specify a location for the Database files. (You can leave the options the installer suggests).
14.On the next screen specify the Database Schema passwords. On a development testing environment you might prefer to use the same password for all database users, so select the 2nd radiobutton.
15.On the next screen you specify the instance name and ias_admin password. For instance name I prefer to use the same as for the Application Server installation name , ie oracleasInfra
16.Next screen is a summary of the Infrastructure installation, if everything is ok, click install. The installation begins.
17.At 33% the installer will ask you for the second CD. You need to find the path to Disk2 and click continue. The same happens afterwards for Disk3.
18.Afterwards all the configuration assistants are being executed. If everything goes ok, perfect, else read their logs to correct any problems. When the configuration ends, on the screen appears an information screen, with the urls form which you can access the Http Server end the management console. Note these urls if you are not very familiar with Oracle Application Server.
19.Next, we are going to install another instance of the Oracle Application Server, containing the Middle Tier and the Portal. So we go again to step 5 and run setup.exe .
20.On the specify file locations target section specify a name like oracleasPortal and path c:\OraHome_Portal
21.On the select a product to install screen, we now must choose option 1 Oracle Application Server 10g.
22.On the next screen ?Select Installation type? we must choose at least option 2 (Portal and Wireless). We may also choose option 3: Business Intelligence and Forms, as this type of installation includes Portal, but it also includes things that are not requirements for a Portal.
23.Next screen needs our confirmation that we have administrator privileges.
24.Following we need to specify Configuration Options. From the available components we only need Oracle Portal (Wireless is not required).
25.On the next screen we leave ports to automatic.
26.On the next screen we need to register our AS with the Internet Directory (we installed it as part of the infrastructure previously). So we specify the hostname of that machine (or IP address) and the port of the OID (Oracle Internet Directory). The default value is 389.
27.On the next step we specify the OID Login credentials. We have specified them in the Infrastructure installation.
28.On the next screen we specify the connect string to the Metadata Repository (that is the Oracle Database that was installed as part of the Infrastructure). We leave it as is.
29.Then again as above, we need to specify the instance name and ias_admin password. The instance name can be something like oracleasPortal etc.
30.On the next screen ?Summary? we review the components to be installed and begin with the proccess. Again we need to specify paths to Disks2 and 3 when requested by the installer.
31.If everything goes ok we can access the HTTP Server on http://<hostname>:80 and the Portal on http://<hostname>/pls/portal
32.Login to your portal with username portal and the password you specified for the database user system! Congrats.
We have to do one last step. Upgrade the Portal Installation from 10.1.2 to 10.1.4 For that we will use the last CD we downloaded.
1.First we go to the Middle Tier Application Server and Stop all Middle-Tier Instances. Login through http://yourhost:18100 with username ias_admin and the password you specified and on the Application Server: oracleasPortal.youhost Home click Stop All
2.We copy it directly under C:\ and run the following command to go to a required directory: cd C:\as_windows_x86_portal_1014\mrua_051020\mrua
3.Then, run the following command from CMD: mrua.bat -oracle_home C:\OraHome_Portal -oid_host youhost -oid_ssl_port 636
which is in general: <mrua_script> -oracle_home <metadata_repository_oracle_home> -oid_host <oracle_internet_directory_host> -oid_ssl_port <oracle_internet_directory_ssl_port>