XDB enable use of FTP and HTML protocols to access and manage files located on ASM disksgroups. Files can be easily browse or moved in/out from ASM this way.

To set up the FTP access, We must first set up the Oracle XML DB access to the ASM folders. We can do this by executing the catxdbdbca.sql script, found in the $ORACLE_HOME/rdbms/admin directory. The script takes two parameters: the port numbers for the FTP and HTTP services, respectively. So I run the script as follows:

Sqlplus>connect sys/oracle @catxdbdbca 7787 8080

Running the script enables an FTP service listening on port 7787 and an HTTP service on port 8080. If you look at your listener status it should display the 2 new ports.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
————————
Alias listener
Version TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date 05-AUG-2009 17:44:48
Uptime 140 days 21 hr. 2 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=7787))(Presentation=FTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW))

Use FTP to access ASM files : We can access the ASM folders from an external source, using a regular FTP client like filezilla.

ftp> open localhost 7787
Connected to localhost
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 wygtstorlocalhost FTP Server (Oracle XML DB/Oracle Database) ready.
530 Please login with USER and PASS.
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (localhost:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> cd /sys/asm : ASM volumes stored here
250 CWD Command successful
ftp> ls
200 PORT Command successful
150 ASCII Data Connection
DATA
BACKUP
ftp: 21 bytes received in 0.48Seconds 0.04Kbytes/sec.

Use HTTP to access ASM files : Let’s use a browser to access asm files

My servername is localhost so I have to use URL http://localhost:8080. I entered oracle system user and password at credentials. The next screen shows the list of directories

PURPOSE : Provide a summary report of all disk groups.

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN group_name FORMAT a20 HEAD ‘Disk Group|Name’
COLUMN sector_size FORMAT 99,999 HEAD ‘Sector|Size’
COLUMN block_size FORMAT 99,999 HEAD ‘Block|Size’
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD ‘Allocation|Unit Size’
COLUMN state FORMAT a11 HEAD ‘State’
COLUMN type FORMAT a6 HEAD ‘Type’
COLUMN total_mb FORMAT 999,999,999 HEAD ‘Total Size (MB)’
COLUMN used_mb FORMAT 999,999,999 HEAD ‘Used Size (MB)’
COLUMN pct_used FORMAT 999.99 HEAD ‘Pct. Used’

break on report on disk_group_name skip 1
compute sum label “Grand Total: ” of total_mb used_mb on report

SELECT   name group_name,
sector_size sector_size,
block_size block_size,
allocation_unit_size allocation_unit_size,
state state,
TYPE TYPE,
total_mb total_mb,
(total_mb – free_mb) used_mb,
ROUND ( (1 – (free_mb / total_mb)) * 100, 2) pct_used
FROM   v$asm_diskgroup
ORDER BY   name

Sample Report

Disk Group Sector Block Allocation
Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used
——————– ——- ——- ———— ———– —— ————— ————– ———
XYZ_REDO_DG01 512 4,096 16,777,216 MOUNTED EXTERN 28,144 9,424 33.48
ABC_ARCH_DG00 512 4,096 16,777,216 MOUNTED EXTERN 225,216 28,656 12.72
ABC_DATA_DG00 512 4,096 16,777,216 MOUNTED EXTERN 450,432 88,800 19.71
ABC_FLBK_DG00 512 4,096 16,777,216 MOUNTED EXTERN 112,608 4,848 4.31
ABC_REDO_DG00 512 4,096 16,777,216 MOUNTED EXTERN 28,128 9,584 34.07
ABC_REDO_DG01 512 4,096 16,777,216 MOUNTED EXTERN 28,128 9,456 33.62
————— ————–
Grand Total: 4,448,192 2,110,496

— +—————————————————————————-+
— | Jeffrey M. Hunter |
— |—————————————————————————-|
— | PURPOSE : Provide a summary report of all disks contained within all ASM |
— | disk groups along with their performance metrics. |
— | NOTE : As with any code, ensure to test this script in a development |
— | environment before attempting to run it in production. |
— +—————————————————————————-+

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN disk_group_name FORMAT a20 HEAD ‘Disk Group Name’
COLUMN disk_path FORMAT a20 HEAD ‘Disk Path’
COLUMN reads FORMAT 999,999,999 HEAD ‘Reads’
COLUMN writes FORMAT 999,999,999 HEAD ‘Writes’
COLUMN read_errs FORMAT 999,999 HEAD ‘Read|Errors’
COLUMN write_errs FORMAT 999,999 HEAD ‘Write|Errors’
COLUMN read_time FORMAT 999,999,999 HEAD ‘Read|Time’
COLUMN write_time FORMAT 999,999,999 HEAD ‘Write|Time’
COLUMN bytes_read FORMAT 999,999,999,999 HEAD ‘Bytes|Read’
COLUMN bytes_written FORMAT 999,999,999,999 HEAD ‘Bytes|Written’

break on report on disk_group_name skip 2

compute sum label “” of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on disk_group_name
compute sum label “Grand Total: ” of reads writes read_errs write_errs read_time write_time bytes_read bytes_written on report

SELECT
a.name disk_group_name
, b.path disk_path
, b.reads reads
, b.writes writes
, b.read_errs read_errs
, b.write_errs write_errs
, b.read_time read_time
, b.write_time write_time
, b.bytes_read bytes_read
, b.bytes_written bytes_written
FROM
v$asm_diskgroup a JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name
/

cd Changes the current directory to the specified directory.

duDisplays the total disk space occupied by ASM files in the specified ASM directory and all its subdirectories, recursively.

exit Exits ASMCMD.

find Lists the paths of all occurrences of the specified name (with wildcards) under the specified directory.
ASMCMD> find +dgroup1 undo* +dgroup1/SAMPLE/DATAFILE/UNDOTBS1.258.555341963 +dgroup1/SAMPLE/DATAFILE/UNDOTBS1.272.557429239

The following example returns the absolute path of all the control files in the
+dgroup1/sample directory.ASMCMD> find -t CONTROLFILE +dgroup1/sample * +dgroup1/sample/CONTROLFILE/Current.260.555342185 +dgroup1/sample/CONTROLFILE/Current.261.555342183

ls Lists the contents of an ASM directory, the attributes of the specified file, or the names and attributes of all disk groups.

lsct Lists information about current ASM clients.

lsdg Lists all disk groups and their attributes.

mkalias Creates an alias for a system-generated filename.

mkdir Creates ASM directories.

pwd Displays the path of the current ASM directory.

rm Deletes the specified ASM files or directories.

rmalias Deletes the specified alias, retaining the file that the alias points to.

Read more…

From the OS command prompt, type asmcmd, which brings up the ASM command-line prompt:

Read more…

In theory there is no need to start services as they are brought online by Oracle Clusterware

– Copy dbora at /etc/init.d directory

# cd /etc/init.d # chmod 755 dbora # chown root:root dbora # ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora # ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora # ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

–  Change oratab entries from “N” to “Y” 

+ASM:/u01/app/oracle/product/10.1.0/db_1:Y      
TESTDB:/u01/app/oracle/product/10.1.0/db_1:Y   

–  Manually edit /etc/inittab so that entry to respawn init.cssd comes before running the runlevel 3

Orignal /etc/inittab file: (…)
# System initialization.
l5:5:wait:/etc/rc.d/rc 5
l6:6:wait:/etc/rc.d/rc 6
(…)
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null

Modified /etc/inittab file: (…)
# System initialization.
si::sysinit:/etc/rc.d/rc.sysinit
l0:0:wait:/etc/rc.d/rc 0
l1:1:wait:/etc/rc.d/rc 1
l2:2:wait:/etc/rc.d/rc 2
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null
l3:3:wait:/etc/rc.d/rc 3
(…)

Oracle Clusterware installed OK. ASM1 instance works OK while ASM2 would return ORA-15186/ORA-15063

Read more…