Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, November 28, 2010

Access ASM Disk files through FTP and HTTP (Oracle Databse10g/11g)

XDB configurations enable the possibility to use FTP/HTTP from an ftp and HTTP session on unix or through a browser on Windows.
Files can be easily moved in/out from ASM in this way.
Step 1: Install XDB Schema
        1.1 Connect as sysdba and run the catqm.sql script. Maintain a spool of the script running:
              UNIX: $ORACLE_HOME/rdbms/admin subdirectory
              WINDOWS: $ORACLE_HOME\rdbms\admin subdirectory
             The catqm.sql script requires the following parameters be passed to it when run:
             A. XDB user password
             B. XDB user default tablespace (You can use any tablespace other than system undo and
             temp. This   tablespace has to exist prior to running the script.)
            C. XDB user temporary tablespace
            Therefore the syntax to run catqm.sql will be:
            SQL>@catqm.sql A B C

            For Example:
            SQL> set echo on
            SQL>spool xdb_install.log
            SQL>;@?/rdbms/admin/catqm.sql XDB XDB TEMP
       1.2 If the following line is not already apart of the database system parameters (init.ora/spfile).
            NOTE: PLEASE REPLACE ,instanceid1,2 etc with your actual values
            a. Non-RAC
           dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)" eg; "(PROTOCOL=TCP)
           (SERVICE=ASMDBXDB)" for my testing
            b. RAC
            instanceid1.dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"
             instanceid2.dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"
            c.If you are not using the default Listener ensure you have set LOCAL_LISTENER in the
            as prescribed for RAC/NON-RAC instances or the end points will not register.

       1.3 Check for any invalid XDB owned objects:
            SQL> select count(*) from dba_objects
                       where owner='XDB' and status='INVALID';
       1.4 Check DBA_REGISTRY for XDB status:
             SQL> select comp_name, status, version from DBA_REGISTRY where comp_name='Oracle
                        XML Database'
              The results should indicate the correct version and patch in a valid status.

              Note: If something goes bad see the Note:243554.1 for details.
Step 2: Configure the FTP and HTTP ports of XDB :
        connect / as sysdba
        execute dbms_xdb.sethttpport(8080);
        execute dbms_xdb.setftpport(2100);

Step 3: Restart the database and the listener:
        sqlplus '/ as sysdba'
        shutdown immediate
        lsnrctl stop
        lsnrctl start

Step 4: Check the listener status, you will notice that the following listening endpoints were automatically registered with your listener
        (DESCRIPTION =(ADDRESS = (PROTOCOL = tcp)(HOST = SRV2)(PORT = 2100))
        (Presentation = FTP)(Session = RAW))
        (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = SRV2)(PORT = 8080))
        (Presentation = HTTP)(Session = RAW))

Step 5: Connect to the ftp as follows:
        C:\Documents and Settings\inam> ftp -n
        C:\Documents and Settings\inam>open SRV2 2100
        C:\Documents and Settings\inam>user system
        C:\Documents and Settings\inam>cd sys
        C:\Documents and Settings\inam>cd asm
        Note: SRV2 is the machine where Oracle DB is running, system is the database user, sys and asm are folder which you are accessing.
        You can access via browser also by hitting like ftp://srv2:2100/. If you click on files you will get the option to save it on your
        desired location. Try any ftp client like FileZilla and you will be able to navigate through ASM directories and drag and drop files
        in and out of ASM.

Step 6: Connect via HTTP
        In IE hit http://srv2:8080/sys/asm, you will be prompted for the user and password , provide system as username with its password. 

Using Windows XP
To use the Add Network Place Wizard to add a shortcut to your computer that allows you to upload and access files in Resources or Drop Box using WebDAV:
  1. On the desktop, double-click My Network Places.

  2. In the "Network Tasks" pane, click Add a network place.

  3. On the welcome screen, click Next.

  4. Select Choose another network location, and then click Next.

  5. In the "Internet or network address:" field, enter a URL that points to the destination Resources or Drop Box tool. For example ftp://srv2:2100/sys/asm/DB_DATA/ASMDB
You will see the files in explorer and you can use the standard copy/paste operations.
FileZilla client is being shown to see asm diskgroups with files copied on local drive.

Accessing asm diskgroups using IE through FTP

Accessing asm diskgroups by WebFolder (WebDav)
Accessing asm diskgroups using IE through HTTP

For 11g
XDB is by default available but you need to set the HTTP and FTP ports

  1* select comp_name, status, version from DBA_REGISTRY where comp_name='Oracle XML Database'
SQL> /

----------- ------------------------------
Oracle XML Database
SQL> select DBMS_XDB.GETHTTPPORT from dual;
SQL> select DBMS_XDB.GETFTPPORT from dual;
SQL> execute dbms_xdb.sethttpport(8080);
PL/SQL procedure successfully completed.
SQL> execute dbms_xdb.setftpport(2100);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

Check the listener status after setting ports

Perform other steps to work with XDB as mentioned above. 
Note: If you face any issue while connecting throgh Filezilla then try Active connection instead of Passive using Edit->Settings in Filezilla

No comments: