Commanding Asm

Connecting to ASM through the TNS Listener from a Client Desktop by Jeff Hunter, Sr. Database Administrator

Contents 1. 2. 3. 4. 5.

Overview Modify the listener.ora for the ASM Instances Add ASM Entries to the tnsnames.ora File on the Client Machine Bounce the TNS Listener Test Access to ASM from the Client Machine

Overview By default, the Oracle Database Configuration Assistant (DBCA) does not configure the TNS listener to accept client requests to an ASM instance running from a different node. Access will be denied for clients like SQL*Plus, Perl DBI:DBD, and JDBC when attempting to connect to an ASM instance from a node other than the node running the ASM instance. When the service is created for an ASM instance, its status is BLOCKED: [oracle@linux1 ~]$ lsnrctl status LISTENER_LINUX1 | grep ASM Service "+ASM" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service... Service "+ASM_XPT" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...

This limitation puts restrictions on scripts and other client tools that want to monitor and manage an ASM instance from a different node. Getting around this restriction however is an easy task that involves manually creating a service name for the ASM instance. This article presents the steps required to access an ASM instance through the TNS listener from a client desktop. The database used in this article is a two-node Oracle RAC 10g clustered database where in fact there will two ASM instances (one ASM instance for each Oracle instance in the cluster). The database version is Oracle 10g Release 2 (10.2.0.3.0) running on CentOS 4.5 (or RHEL 4.5): Node 1 Machine Name:

linux1.idevelopment.info

Oracle SID:

orcl1

ASM SID:

+ASM1

ASM Global DB Name (service +ASM name):

Node 2 Machine Name:

linux2.idevelopment.info

Oracle SID:

orcl2

ASM SID:

+ASM2

ASM Global DB Name (service +ASM name):

Modify the listener.ora for the ASM Instances The first step is to modify the listener.ora file for the ORACLE_HOME running ASM on all nodes in the RAC cluster by adding a new service: Node 1 - (listener.ora) LISTENER_LINUX1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521)(IP = FIRST)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)(IP = FIRST)) ) ) SID_LIST_LISTENER_LINUX1 = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = +ASM) (SID_NAME = +ASM1) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) ) )

Node 2 - (listener.ora) LISTENER_LINUX2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521)(IP = FIRST)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521)(IP = FIRST)) ) ) SID_LIST_LISTENER_LINUX2 = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = +ASM) (SID_NAME = +ASM2) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) ) )

Add ASM Entries to the tnsnames.ora File on the Client Machine The next step is to add ASM entries to the tnsnames.ora file on the client machine that will be connecting to the ASM instance(s). The client machine in this example is named alex.idevelopment.info. A separate tnsnames entry will be created for each ASM instance in the two-node RAC. The two tnsnames entries for this example are named ORCL1_ASM1 and ORCL2_ASM2: Client Node - (tnsnames.ora) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.idevelopment.info) ) ) ORCL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.idevelopment.info) (INSTANCE_NAME = orcl1) ) ) ORCL2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.idevelopment.info) (INSTANCE_NAME = orcl2) ) ) ORCL1_ASM1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = +ASM) ) ) ORCL2_ASM2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = +ASM) ) )

ORCL_TAF = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl_taf.idevelopment.info) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) )

Bounce the TNS Listener The final step is to bounce the Oracle TNS Listener. Remember that the listener process will need to be bounced on both of the nodes in the RAC cluster: # ----------# FROM linux1 # ----------[oracle@linux1 ~]$ lsnrctl stop LISTENER_LINUX1 [oracle@linux1 ~]$ lsnrctl start LISTENER_LINUX1 # ----------# FROM linux2 # ----------[oracle@linux2 ~]$ lsnrctl stop LISTENER_LINUX2 [oracle@linux2 ~]$ lsnrctl start LISTENER_LINUX2

After restarting the TNS listener, the new service should be available from both nodes in the RAC cluster: # ----------# FROM linux1 # ----------[oracle@linux1 ~]$ lsnrctl status LISTENER_LINUX1 | grep Service "+ASM" has 2 instance(s). Instance "+ASM1", status UNKNOWN, has 1 handler(s) for Instance "+ASM1", status BLOCKED, has 1 handler(s) for Service "+ASM_XPT" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for # ----------# FROM linux2 # ----------[oracle@linux2 ~]$ lsnrctl status LISTENER_LINUX2 | grep Service "+ASM" has 2 instance(s). Instance "+ASM2", status UNKNOWN, has 1 handler(s) for Instance "+ASM2", status BLOCKED, has 1 handler(s) for Service "+ASM_XPT" has 1 instance(s). Instance "+ASM2", status BLOCKED, has 1 handler(s) for

ASM this service... this service... this service...

ASM this service... this service... this service...

Test Access to ASM from the Client Machine After the new ASM service(s) have been added and the TNS listener successfully restarted, test access to the ASM instances from the client machine using SQL*Plus: ORA10G on alex: sqlplus "sys/@orcl1_asm1 as sysdba" @asm_diskgroups.sql Disk Group Sector Block Allocation Name Size Size Unit Size (MB) Used Size (MB) Pct. Used -------------------- ------- ------- -------------------------- -------------- --------FLASH_RECOVERY_AREA 512 4,096 1,048,576 596,985 339 .06 ORCL_DATA1 512 4,096 1,048,576 597,017 40,784 6.83

State

Type

Total Size

----------- -----MOUNTED

EXTERN

MOUNTED

EXTERN -------------

-- -------------Grand Total: 1,194,002 41,123

ORA10G on alex: sqlplus "sys/@orcl2_asm2 as sysdba" @asm_diskgroups.sql Disk Group Sector Block Allocation Name Size Size Unit Size (MB) Used Size (MB) Pct. Used -------------------- ------- ------- -------------------------- -------------- --------FLASH_RECOVERY_AREA 512 4,096 1,048,576 596,985 339 .06 ORCL_DATA1 512 4,096 1,048,576 597,017 40,784 6.83

State

Type

Total Size

----------- -----MOUNTED

EXTERN

MOUNTED

EXTERN

-- -------------Grand Total: 1,194,002 41,123

-------------

Commanding ASM By Arup Nanda Access, transfer, and administer ASM files without SQL commands. Oracle Database 10g Release 1 introduced Automatic Storage Management (ASM), a new framework for managing Oracle database files. 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 includes volume management functionality similar to that of a generic logical volume manager (LVM). Managing ASM through SQL interfaces in Oracle Database 10g Release 1 posed a challenge for system administrators who were not very familiar with SQL and preferred a more conventional command-line interface. In Oracle Database 10g Release 2, you have an option to manage the ASM files by using ASMCMD—a powerful and easy-to-use command-line tool. In Oracle Database 10g Release 1, ASM disk groups are not visible outside the database for regular file system administration tasks such as copying and creating directories. In Oracle Database 10g Release 2, however, you can transfer the files from ASM to locations outside of the disk groups via FTP and through a Web browser using HTTP. This article shows you how to use these enhancements in ASM to accomplish everyday tasks. Command-Line Tool ASMCMD is included in the installation of the Oracle Database 10g Release 2 software; no setup is necessary. To use ASMCMD, first set the ORACLE_SID to the correct ASM instance. Typically it's named +ASM: export ORACLE_SID=+ASM

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

Common Commands You can invoke the ASMCMD tool with a -p parameter to always display the present directory inside the prompt itself. Once at the prompt, use the cd command to go to a particular directory. To check which directories and files are available inside a directory, use the ls command. Let's see it in action: $ asmcmd -p ASMCMD [+] > ls USERDG1/ USERDG2/ USERDG3/ USERDG4/ USERDG5/ ASMCMD [+] > cd USERDG1 ASMCMD [+USERDG1] >

Note how the prompt changed; it now shows the current directory (USERDG1). When you want to create a directory, use the mkdir command as follows: ASMCMD [+USERDG1] > mkdir test

To remove a directory, use the rm command, which removes both files and directories.

Advanced Commands To display the details of the files and directories, rather than simple names, use the -l parameter with the ls command. Listing 1 presents a sample ls -l command and result. Let's look at the output. Listing 1: The -l modifier to ls ASMCMD [+USERDG2/DBA102] > ls -l Type Redund Striped Time

Sys Name Y CONTROLFILE/ Y DATAFILE/ Y ONLINELOG/ Y TEMPFILE/ N control01.ctl => +USERDG2/DBA102/CONTROLFILE/Current.260.573852215 N control02.ctl => +USERDG2/DBA102/CONTROLFILE/Current.261.573852215 N control03.ctl => +USERDG2/DBA102/CONTROLFILE/Current.262.573852215 N example01.dbf => +USERDG2/DBA102/DATAFILE/UNKNOWN.267.573852295 N redo01.log => +USERDG2/DBA102/ONLINELOG/group_1.263.573852243 N redo02.log => +USERDG2/DBA102/ONLINELOG/group_2.264.573852249 N redo03.log => +USERDG2/DBA102/ONLINELOG/group_3.265.573852255 N sysaux01.dbf => +USERDG2/DBA102/DATAFILE/SYSAUX.257.573852115 N system01.dbf => +USERDG2/DBA102/DATAFILE/SYSTEM.256.573852113 N temp01.dbf => +USERDG2/DBA102/TEMPFILE/TEMP.266.573852277 N undotbs01.dbf => +USERDG2/DBA102/DATAFILE/UNDOTBS1.258.573852115 N users01.dbf => +USERDG2

In Listing 1, the last column—Name—shows the name of the file or the directory. Both the real names and the aliases are reported. For instance, in the output in Listing 1, the alias control01.ctl refers to the real ASM file +USERDG2/DBA102/CONTROLFILE/Current.260.573852215. The Sys column—immediately to the left of the Name column—shows if the file or directory was created by the ASM system. In the Listing 1 output, for instance, the CONTROLFILE directory was created by SYSTEM, so the Sys flag is Y. This directory was created when the database was created. Because the CONTROLFILE directory is not a real file but an alias, the attributes of the alias—such as size, free space, and redundancy—shown in the first few columns of the output are null. If you use the same ls -l command for the actual file— +USERDG2/DBA102/CONTROLFILE/Current.260.573852215—as you did for the alias, you will get the attributes in the output. To see file space usage information, use the -ls option with the ls command, as shown in Listing 2. Let's look at the output of Listing 2.

The Type column indicates the type of the file. In this example, the type of all three files is CONTROLFILE. Depending on the type of the file, the values in this column can be any of the following: • • • • • • • • • • • • •

CONTROLFILE DATAFILE ONLINELOG ARCHIVELOG TEMPFILE BACKUPSET XTRANSPORT PARAMETERFILE DATAGUARDCONFIG FLASHBACK CHANGETRACKING DUMPSET AUTOBACKUP

Each file is in a disk group that is on only one physical disk, so the Redund column in Listing 2 shows UNPROT (for unprotected). (Note that the disk group might be mirrored at the hardware level, which ASM is not aware of. In such a case, ASM still reports it as unprotected.) The files in Listing 2 are control files, and they are striped finely by ASM, hence the Striped column shows FINE. The Time column shows when each file was created. The Sys column for each file shows Y, indicating that the control files were created by the ASM system, not by the user. The Blocks and Block_Size columns in Listing 2 show the number of blocks and the size of each block in bytes in each file, respectively. The total file size in bytes is shown in the Bytes column. The actual space allocated to each file may be more than the actual size of the file and is shown in bytes in the Space column. To find out how much space is used in a directory, you can issue the du command, as follows: ASMCMD [+USERDG1] > du . Used_MB Mirror_used_MB 14 14

The total space used in the directory is shown in the Used_MB column. If the disk group has been mirrored, the mirrored space is shown in the Mirror_used_MB column. In this case, they are the same, because the disk groups are not mirrored. To find out about the disk groups in the ASM storage, you can use thelsdg command, as shown in Listing 3. The name of the disk group is the last column, Name. The Type column shows the type of redundancy of the disk group. In Listing 3, the Type column shows EXTERN for all groups, because the groups were created as externally mirrored. The State column shows the status of the disks— MOUNTED, DISMOUNTED, and so on. If the ASM disk group needs rebalancing, the Unbal column shows Y. The total space available in the disk group is shown in the Total_MB column in Listing 3, and the unused capacity is shown in the Free_MB column. If I had used ASM mirroring instead of hardwarelevel external mirroring, some space would have been required for the mirroring. This space is shown in the Req_mir_free_MB column. The value of the Usable_file_MB column shows the file space available, and the value is the same as the result of Free_MB - Req_mir_free_MB.

An ASM instance serves as a storage container; it's not a database by itself. Other databases use the space in the ASM instance for datafiles, control files, and so on. How do you know how many databases are using an ASM instance? A simple lsct command will show you that information. Listing 4 shows the lsct command and output. The names of the databases using this ASM instance are shown in the DB_Name column. Code Listing 4: Displaying the real ASM file attributes ASMCMD [+USERDG1] > lsct DB_Name Status Instance_Name DBA102 CONNECTED DBA102 EMREP CONNECTED

Software_Version

Compatible_version

10.2.0.1.0

10.2.0.1.0

10.2.0.1.0

10.2.0.1.0

EMREP

FTP and HTTP Access Because ASM is not a regular file system, you can't use the standard FTP and HTTP services to access these files. To access them, you can use the file mapping functionalities provided by the Oracle XML Database (Oracle XML DB) feature. This section shows you how to set up FTP and HTTP to access the ASM files and transfer them as regular OS files. This access requires a one-time setup. Setup. FTP and HTTP services are mapped to a port on the server. By default, the FTP and HTTP services run on ports 21 and 80, respectively. However, ASM FTP and HTTP do not use the default services; they use the specialized services provided by Oracle XML DB. You have to use two different ports—one for FTP and the other for HTTP services. Typically, Oracle users choose 7777 for FTP and 8080 for HTTP, but you are free to choose any unused port number. For my setup, I want to use 8080 for HTTP and 7787 for FTP. I use the 7787 port deliberately, to show how different port numbers can be used. To set up the FTP access, I must first set up the Oracle XML DB access to the ASM folders. I 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: @catxdbdbca 7787 8080

Running the script enables an FTP service listening on port 7787 and an HTTP service on port 8080. I can access the ASM folders from an external source, using a regular FTP client (such as ftp.exe, provided in Windows). Listing 5 shows a sample FTP session on a Windows system accessing ASM folders. To clarify the explanation, I have prefixed each line with a line number; the line numbers do not appear in the actual output. Code Listing 5: FTP interaction with ASM files 1. 2. 3. 4. 5.

C:\WUTemp>ftp ftp> open prolin1 7787 Connected to prolin1. 220- prolin1 Unauthorized use of this FTP server is prohibited and may be subject to civil and criminal prosecution.

6. 220 prolin1 FTP Server (Oracle XML DB/Oracle Database) ready. 7. User (prolin1:(none)): system 8. 331 pass required for SYSTEM 9. Password: 10. 230 SYSTEM logged in 11. ftp> cd /sys/asm 12. 250 CWD Command successful 13. ftp> ls 14. 200 PORT Command successful 15. 150 ASCII Data Connection 16. USERDG5 17. USERDG4 18. USERDG3 19. USERDG2 20. USERDG1 21. 226 ASCII Transfer Complete 22. ftp: 45 bytes received in 0.26Seconds 0.17Kbytes/sec. 23. ftp> cd USERDG2 24. 250 CWD Command successful 25. ftp> ls 26. 200 PORT Command successful 27. 150 ASCII Data Connection 28. emrep 29. DBA102 30. 226 ASCII Transfer Complete 31. ftp: 15 bytes received in 0.01Seconds 1.50Kbytes/sec. 32. ftp> cd DBA102 33. 250 CWD Command successful 34. ftp> ls 35. 200 PORT Command successful 36. 150 ASCII Data Connection 37. DATAFILE 38. system01.dbf 39. sysaux01.dbf 40. undotbs01.dbf 41. users01.dbf 42. CONTROLFILE 43. control01.ctl 44. control02.ctl 45. control03.ctl 46. ONLINELOG 47. redo01.log 48. redo02.log 49. redo03.log 50. TEMPFILE 51. temp01.dbf 52. example01.dbf 53. 226 ASCII Transfer Complete 54. ftp: 208 bytes received in 0.02Seconds 10. 40Kbytes/sec. 55. ftp> bin 56. 200 Type set to I. 57. ftp> get users01.dbf 58. 200 PORT Command successful 59. 150 BIN Data Connection 60. 226 BIN Transfer Complete 61. ftp: 5251072 bytes received in 7.97Seconds 658.69Kbytes/sec.

Lines 1 through 6 in Listing 5 show how I connect to the FTP server running on the server named prolin1 on port 7787. Line 6 shows the FTP server as being of type Oracle XML DB/Oracle Database. Lines 7 and 8 show how I enter the user and password to connect to the server. Because ASM resides on a database, this is actually the database user and password. In this case, I have used the SYSTEM database user. Once logged in, I can go to the directory containing the files to start the FTP process. But ASM files are not really file system files; they are pointers that appear as files to the database only. How can FTP see ASM files and directories? This is where the Oracle XML DB access makes it easier. Through the Oracle XML DB FTP port, ASM disk groups are available outside the database via a virtual file system: /sys/asm. In line 11, I change the directory to the virtual file system /sys/asm. In line 13, I issue an ls command to see which files are available. It shows the contents of the ASM storage (lines 16 through 20). Then I can change the directory to any one of them, such as USERDG2 (in line 23). Finally, I transfer the users01.dbf file. Because this is an Oracle datafile, I FTP it by using binary mode —not the default ASCII—which I set with the bin command (line 55). Then I transfer the file to the local directory, by issuing the get command (line 57). This transfers the virtual file from ASM storage and creates an OS file with the same name (users01.dbf). When you use ASM storage for Oracle Data Pump dump files, this is a perfect way to FTP them to a remote location. HTTP Access. Recall from the last section that I created two ports—one for FTP (7787) and one for HTTP (8080)—and showed how I used FTP on port 7787. I can download an ASM file with a Web browser as well, by using the HTTP port. Because I assigned port 8080 for HTTP, I access the Web listener running on that port. I type the following URL into my browser: http://prolin1:8080

The browser connects to Oracle XML DB via HTTP and displays the results shown in Figure 1. I click on the hyperlink sys and then asm; I then see all the disk groups. I can click on the individual files to download them to the local disk.

Figure 1: ASM files accessed by HTML client

Conclusion Command-line ASM management with ASMCMD requires no SQL. It also opens up possibilities for scripting ASM operations. The FTP and HTTP interfaces—available through Oracle XML DB— provide access to ASM files and the ability to copy them and use them as conventional OS files.