Sunday, March 2, 2008

ASM File Handling.

Automatic Storage Management (ASM) is a feature of Oracle Database 10g that provides integrated cluster file system and volume management capabilities at no additional cost. ASM provided a foundation for highly efficient storage management with kernelized asynchronous I/O, direct I/O, redundancy, striping, and an easy way to manage storage.

ASM disk groups are not visible outside the database for regular file system administration tasks such as copying and creating directories. It is always seems to be difficult task to backup or migrating the database. But in reality it is not the case. Oracle has provided various methods to deal with ASM files, discussed in following sections –

ASM FILES HANDLING USING FTP

Starting with oracle 10g Release 2, Oracle has provided an option to transfer the files from ASM to locations outside of the diskgroups via FTP and ussing HTTP web browser.

To access ASM files, file-mapping functionalities can be used provided by Oracle XML DB feature. Following is the method to setup FTP and HTTP to access and trasnfer the ASM files to regular OS files. FTP uses 21 as defualt port and HTTP uses port 80. ASM do not use default services, so to access ASM files, it is required to use two different unused ports. For e.g. we will use 8080 port for HTTP and 2100 port for FTP.

1. Install XMLDB feature.

   # cd $ORACLE_HOME/rdbms/admin
# sqlplus “/ as sysdba”

SQL> @catqm.sql XDB SYSAUX TEMP

Here XDB is username
SYSAUX is default tablespace for xdb user.
TEMP is temporary tablespace for xdb user.

SQL> @catxdbdbca 2100 8080

Here 2100 is FTP port.
8080 is HTTP port.

Check for any invalid XDB objects and compile, if any.


SQL> select object_name, object_type, status
from dba_objects
where owner=’XDB’ and status=’INVALID’;

SQL> select comp_name, status, version from dba_registry
where comp_name=’Oracle XML Database’;


2. Configure FTP and HTTP Ports.

   SQL> execute dbms_xdb.sethttpport(8080);
SQL> execute dbms_xdb.setftpport(2100);


You can also check the ports being used.

   Select dbms_xdb.gethttpport(), dbms_xdb.getftpport() 
from dual;


3. Listener and Instance modifications

For Single Instance –

SQL> alter system set dispatchers = ‘(PROTOCOL=TCP)
(SERVICE=XDB)’ scope=both;


For RAC Instance –

   SQL> alter system set dispatchers = ‘(PROTOCOL=TCP) 
(SERVICE=XDB)’ scope=both sid=INSTANCE1;

SQL> alter system set dispatchers = ‘(PROTOCOL=TCP)
(SERVICE=XDB)’ scope=both sid=INSTANCE2;


Restart the listener

   # lsnrctl stop
# lsnrctl start


Listener should show http and ftp port entries.

# lsnrctl status

………… Truncated some entries ……………

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=2100))(Presentation=FTP)(Session=RAW))


4. Use FTP at O/S level to do interaction with ASM files.

   C:\> ftp 
ftp> open test 2100 ---- Here test is the server name and 2100 is ftp port.
Username: system ---- System is the database user.
Password: ****** ---- Password of database system user.

ftp> cd /sys/asm


Now directory can be changed to the directory containing the files to start FTP service and ls, get or put commands can be used as normal o/s level ftp command.


DBMS_FILE_TRANSFER Utility

Oracle also provides another feature to move files. The dbms_file_transfer utility introduced by oracle to copy/transfer the files to and from ASM. If you want to copy the file that is attached to a tablespace, you must put that tablespace into read-only mode or take it offline. Because of this limitation, you cannot copy the data files associated with the system, undo, sysaux or temporary tablespace. This utility treats the file as binary file during copy also character conversion is not possible during copy.

Following is the method to transfer the files from file-system to ASM.

List the file to copy.

SQL> select file_name from dba_data_files 
2 where tablespace_name=’USERS’;


Create directory for source and destination.

SQL> create directory source_osfs as ‘/oracle/datafile’;
SQL> create directory dest_asmfs as ‘+asmdsk1/orcl/datafile';
SQL> grant read, write on source_osfs to testuser;
SQL> grant read, write on dest_asmfs to testuser;


Connect to the user and transfer the file.

SQL> connect testuser/testuser@ora10gdb
SQL> alter tablespace users read only;
-OR-
SQL> alter tablespace users offline;


SQL> exec dbms_transfer.copy_file (source_directory_object=>’source_osfs’, source_file_name=>’users01.dbf’, destination_directory_object=>’dest_asmfs’, destination_file_name=>’users01.dbf’);


File has been copied so now datafile path can be changed using “alter database rename command”. Finally make the tablespace online for use.

SQL> alter tablespace users read write;
-OR-
SQL> alter tablespace users online;


We can use get_file and put_file procedures of dbms_transfer package to copy the files across the database using database link.

Limitations –

1. File that we want to copy should be multiple of 512 bytes. Otherwise we would get following error. Also maximum file of file to transfer is 2 terabyte.

ERROR at line 1: 
ORA-19505: failed to identify file "/oracle/datafile/users01.dbf"
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2


2. Till Oracle 10g Rel 1 only following transfer is allowed.

ASM to ASM.
OS file to OS file.

This restriction is no more in Oracle 10g Rel 2. DBMS_TRANSFER has been enhanced to support following combinations of ASM.

ASM to OS file.
OS file to ASM.

RMAN & ASM FILES

So far we have completed two methods to copy the files from ASM to OS file system. Finally how can we forget RMAN utility to interact with ASM Files? Following is the method to copy the files using RMAN utility. We will copy the files from one disk group to another diskgroup.

1. Identify the file.

SQL> select file_name from dba_data_files 
2 where tablespace_name='TBS_TEST';

FILE_NAME
---------------------------------------------------
+ORA_DG/oraprod/datafile/tbs_test.1442.642809147


2. Take the file offline and copy it using rman utility.

SQL> alter database datafile 
2 ‘+ORA_DG/oraprod/datafile/tbs_test.1442.642809147’ offline;

# rman target /

RMAN> COPY DATAFILE ‘+ORA_DG/oraprod/datafile/tbs_test.1442.642809147’ TO ‘+ORA_DG_NEW’;


3. Now file has been copied, we can update the dictionary with the new ASM Diskgroup location.

SQL> ALTER DATABASE RENAME FILE
2 ‘+ORA_DG/oraprod/datafile/tbs_test.1442.642809147’
3 TO ‘+ORA_DG_NEW/oraprod/datafile/tbs_test.264.59829765’;


We got the destination file name from rman copy output.

4. Use RMAN to rename the ASM database file copy.

RMAN> switch datafile ‘+ORA_DG/oraprod/datafile/tbs_test. 264.59829765’ to copy;


5. Finally recover and take the file online.

SQL> Recover datafile ‘+ORA_DG/oraprod/datafile/tbs_test. 264.59829765’;

SQL> alter database datafile ‘+ORA_DG/oraprod/datafile/tbs_test. 264.59829765’ online;


6. Delete the old file from ASM directory.

$ export ORACLE_SID=+ASM
$ sqlplus “/ as sysdba”
SQL> alter diskgroup ora_dg_new drop file datafile ‘+ORA_DG/oraprod/datafile/tbs_test. 264.59829765’;