\\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc Last Revision 02/28/00 by rlw Page 1 of 4, Printed 03/19/03 This exercise is designed to give you some basic information about Oracle Distributed Databases and the referencing cnventions used in writing applications using Oracle Distributed Databases. ORACLE DISTRIBUTED DATABASE CONCEPTS General A distributed database is a set of discrete databases stored on multiple networked computers in such a manner that they may be treated as a single database by client applications. If the database servers are all running the same database server software, the distributed database is known as homogeneous. In this exercise you will be working with a homogenous distributed database consisting of Oracle 8i databases residing on two separate Windows Global Database Names Oracle is designed to accommodate distributed databases involving hundreds of database servers. In complex networks, the Oracle global database naming convention provides a method for assigning unique names to each database . Oracle Global Database Names use dot notation which look sort of like URL’s. For example ‘ SALES.US.AMERICAS.ACME_AUTO.COM ‘ , references a database named SALES in the US.AMERICAS.ACME_AUTO.COM domain. Client applications performing operations on distributed databases reference database objects by appending their Global Database Names. The Oracle domain in the lab is named US.ORACLE.COM. Since our lab network utilizes only 2 servers in a single domain, the naming structure is simplified so that use of the domain name in identifying database objects is optional. Network Service Names Oracle databases and client applications communicate with each other using Oracle’s proprietary networking software known as Net8. Net8 is platform independent, so that databases or clients running on a variety of platforms including Windows, UNIX and LINUX can communicate seamlessly with each other. In net8, databases are assigned Service Names which are mapped to network addresses and database names to identify a specific database. These mappings are normally maintained by database administrators. For this exercise the CS2 and CS6 service name mappings have been created for you. Database Objects and Access Permissions In Oracle every database object (table, view, stored procedure, etc.) has an owner. When an object is created, its creator becomes its owner by default. Also by default, the owner is the only person with permissions to view or change that object. If others are subsequently granted permissions to view or modify an object, they must reference that object by the owners user id. For example, if your user id is SCOTT and you create a table named DEPARTMENT and subsequently grant Mary permission to execute SELECT statements against it, Mary would reference it by including the owners Oracle user id . For example, to execute a select against a table owned by user SCOTT (who has give you select permissions) use SELECT * FROM SCOTT.DEPARTMENT; Oracle Database Links Oracle uses Database Links to manage connections between Oracle databases. Database links can be created by a user with database administrator privileges and may be established with a variety of security options. Database links are ordinarily given the same name as the remote database they are providing access to. The links that you will use in this exercise have been established in the database CS2 (at network service location CS2) and the database CS6 (at network service location CS6). Referencing Database Objects in Distributed Databases If you write an application which references an Oracle distributed database, you must qualify the names of objects on remote databases by appending the @ symbol followed by name of the database link used to access the remote database. For example, to reference the table EMP owned by user DERICK using a Database Link named SALES.US.AMERICAS.ACME_AUTO.COM you could use SELECT * FROM DERICK.EMP@SALES.US.AMERICAS.ACME_AUTO.COM; \\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc Last Revision 02/28/00 by rlw Page 2 of 4, Printed 03/19/03 As indicated earlier, we will be working with only one domain in the SPSU CS lab, so you can omit the Global Database Domain when referencing a database object. Distributed Database Security Security for distributed transactions is managed by granting permissions to database objects and Database Links. The authentication database may be centralized for an Oracle domain or decentralized. In decentralized situations, each database maintains its authentication accounts and users must have identical accounts on each database to be accessed. In the lab, we will use decentralized authentication, so you will need identical userid’s and passwords in both databases. You will be responsible for seeing that your password is the same in both databases before you complete the exercise that begins on page 2 of this handout. Commits and Rollbacks You may recall that when you are working in SQLplus, database record changes are initially written to a temporary state in the database you are working with. The changes you make during a session are available to you, but not to others until you commit them. By default the changes are committed when a session ends. Users or applications may also commit changes with the command 'COMMIT' or erase all changes subsequent to the last COMMIT with the command 'ROLLBACK'; A database transaction is defined as one or more sql statements bounded by COMMIT or ROLLBACK. In a distributed environment, COMMITs and ROLLBACK are especially important to applications processing Distributed Database transactions, because of the potential for network failures. DISTRIBUTED DATABASE EXERCISE Your company has a centralized database at the headquarters location in Atlanta, but has decided to build another database in Seattle, where a new Sales location will be opening soon. Personnel records will be decentralized first to gain experience before decentralizing order processing and other functions. In this exercise you will build the first table in Seattle and use the SQLplus application to execute Distributed Database commands against the distributed database.. Existing employee records reside in tables named EMP, DEPT and SALGRADE in a database named CS2 (service name ‘CS2’) at the headquarters location in Atlanta. The owner of these tables is aaMARY. Related records in Seattle will be maintained in a database named CS6 (Service Name ‘CS6’). You will be the owner of the tables located in Seattle You are located in Seattle and your charge is to set up a distributed database that will allow client applications at any company location to manipulate the EMP, DEPT and SALGRADE tables. To do so, you will need to create public database links in the Atlanta and Seattle databases. To summarize, you will be working with the following Oracle database object identifiers. Atlanta Seattle Oracle Database Name CS2 CS6 Oracle Net8 Service Name CS2 CS6 Database Link Name CS2 Database Link Name CS6 You begin by asking the Database Administrator in Seattle to open an account for you in the CS2 database with the same userid that you use in the CS6 database. You then ask the Network Administrator to make the CS2 and CS6 service name mapping available to your workstation. Both of these activities have been completed for you by the lab staff. 1. Create Database Links In the lab you do not have the permissions necessary to create database links, so these have been created for you by the lab staff. As information, the links were created with the following SQL commands. in Atlanta CREATE PUBLIC DATABASE LINK CS6 USING ‘CS6’; in Seattle CREATE PUBLIC DATABASE LINK CS2 USING ‘CS2’; The PUBLIC key word is included to permit users other than the creator of the links to utilize them. 2. Get access to existing tables \\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc Last Revision 02/28/00 by rlw Page 3 of 4, Printed 03/19/03 Recall that only the owner of a database object can grant access privileges to others. Since aaMARY is the owner of he existing EMP, DEPT and SALGRADE tables in Atlanta, you must ask her to grant you the rights to execute SELECT statements against them. She can do so the following command. GRANT SELECT ON EMP, DEPT, SALGRADE TO myuserid; (where myuserid is the userid you are using in this exercise) For this lab, the database administrator has used the following command to give everyone access to these tables. GRANT SELECT ON EMP, DEPT, SALGRADE TO public; If you would like to try out the grant command, grant access to one of your lab mates. 3. Log into the database in Atlanta (at CS2) and review the existing HR tables Using SQLplus, log in to to the Atlanta database with your userid and password and host string CS2. Since aaMARY owns the tables you are interested in you must include her userid in your references to them. . Use the command SELECT * FROM aaMARY.EMP. to view the contents of the EMP table. Use a similar commands to review the contents of aaMARY.DEPT and aaMARY.SALGRADE. 2. Log into the CS6 database and synchronize your passwords Remember that your user id and password in both database must be identical in order for you to query. If you are enrolled in the advanced database class, your instructor has made arrangements for you to have accounts in Atlanta ( database name CS2, Network Service Name CS2) and Seattle (database name CS6 and Network Service Name CS6) with the same user id. You initial Oracle password in the CS labs is always ‘ temp00 ‘, but some of you will have already changed your password at CS2. If you have done so, log into CS6 and make your password identical to what you use on CS2. If your Oracle userid is myuserid and you wish to change your password to mynewpassword use GRANT CONNECT TO myuserid IDENTIFIED BY mynewpassword; 3. Create a copy of the Emp Table on CS6 While still logged in to CS6 use CREATE TABLE EMP AS SELECT * FROM aaMARY.EMP@CS2 WHERE DEPTNO=200; to clone the existing EMP table structure in Atlanta to a new table in Seattle. Since there is no DEPTNO 200, your new table will not contain data. Then use the command DESCRIBE EMP; to verify that the table has been created properly. 4. Create a primary key for the new table When tables are created with 'AS SELECT' , constraints from the original table are not transferred to the new table. Create a primary key for the new table with ALTER TABLE EMP ADD PRIMARY KEY(EMPNO); 5. Populate the Emp table at CS6 Add some data to the new table. While still logged into CS6, execute the following commands. INSERT INTO EMP VALUES (8001, 'HARRIS', 'SALESMAN', 8000, '01-DEC-99', 2000, 0, 30); INSERT INTO EMP VALUES (8002, 'MORRIS', 'SALESMAN', 8000, '01-DEC-99', 1500, 0, 30); INSERT INTO EMP VALUES (8003, 'GREENE', 'SALESMAN', 8000, '01-DEC-99', 1900, 0, 30); COMMIT; Use SELECT * FROM EMP; to verify that data was correctly entered. 6. Grant the VP Personnel ( aaHARRY ) and aaMARY the right to execute selects against your new table; While still logged into CS6, give aaHARRY permission to execute SELECT against the new table. GRANT SELECT ON EMP to aaHARRY; GRANT SELECT ON EMP TO aaMARY; 7. Perform a query against the distributed database from CS6 \\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc Last Revision 02/28/00 by rlw Page 4 of 4, Printed 03/19/03 You are now ready to perform your first distributed query. Suppose that you need to view EMPNO, ENAME, JOB and SAL fields for all employees. While logged into CS6 with your own userid and password execute the following command SELECT EMPNO, ENAME, JOB, SAL FROM EMP UNION SELECT EMPNO, ENAME, JOB, SAL FROM AAMARY.EMP@CS2; Since you are logged in to the Seattle database, it is not necessary to include your own userid in the reference to the local EMP table. To reference the remote table, however, you must include the Owners userid and append the name of the Database Link used to connect to the remote database. 8 Verify that aaHarry can query the distributed databaase Harry is located in Seattle and needs to list the ENAME, LOC and SAL for employees by their job title.. You decide to write and test a query before giving him instructions on how to do this. Use SQLplus to log in at CS6 with userid aaHARRY and password aaHARRY and execute a query that will return ENAME, LOC, and SAL for all employees whose JOB is salesman. SELECT ENAME, LOC, SAL FROM AAMARY.DEPT@CS2, AAMARY.EMP@CS2 WHERE AAMARY.EMP.JOB='SALESMAN' AND AAMARY.DEPT.DEPTNO=AAMARY.EMP.DEPTNO UNION SELECT ENAME, LOC, SAL FROM AAMARY.DEPT@CS2, myuserid.EMP WHERE myuserid.EMP.JOB='SALESMAN' AND AAMARY.DEPT.DEPTNO=myuserid.EMP.DEPTNO ; 9. Verify that other users in Seattle do not have access to the new table Use SQLplus to log in at CS6 with userid SCOTT and password tiger. Execute the following SELECT * from myuserid.EMP@CS6; You should receive a message to the effect that the table does not exist.
Pages to are hidden for
"mars wwwroot cslab Reference oracle Distributed Database OracleDistDB doc"Please download to view full document