University:
Boston University
Course:
GRS CS 910 | Directed Study: Systems
Academic year:

2020
Views:

361

Pages:

17
Author:

Jaylin Duran

Creating a Database User to Complete Your Assignments Step 1: Creating Your Own User Create the User The SYSTEM account that we are now logged in under is the initial DBA account, and it has so many privileges that it is risky for routine use. We will next create an ordinary user of the sort that we will use for this class. I will call this user “student”, but you can substitute any name. Choose a password that you can remember. Just type in the following: CREATE USER student IDENTIFIED BY studentpassword DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; This SQL command creates user “student” with password “studentpassword” and sets up two important defaults for student’s account. Note the semicolon at the end. Upper case is conventional for SQL keywords in the Oracle community. When you have typed this command, it will look like this: We now need to execute this command for it to take effect in the database. Click the icon to do so. When executed, the results of the command – the fact that the user is created – should display in a pane below the command. Page 38 of 76 Give the User Basic Privileges Next we will give user “student” the privilege to connect to the database and the privilege to create database resources. Type and execute GRANT CONNECT, RESOURCE TO student; ALTER USER student QUOTA UNLIMITED ON USERS; Again, notice the semicolon at the end. Your screen should now look about like this: Page 39 of 76 The new user is now created! Page 40 of 76 Step 2: Connecting with Your New User Disconnect the System User We can use this new user to complete our assignments without concerning ourselves with accidentally damaging the database. So that we are sure not to use the “system” any longer, let us close the connection to “system” by right clicking on the “system” connection, and choosing “Disconnect” from the context menu. This will terminate the system connection, but will not close the SQL command window. You can close the existing SQL command window by clicking the icon. Connect as Your New User Next we will setup a new connection to your new user by following similar steps to setting up a connection to the system user. Right click the “Connections” category, choose “New Connection…” from the context menu, and fill in the appropriate information for that connection. Page 41 of 76 Remember to put in the new username and password instead of the system username and pasword. After inputting the information, click the worksheet for the new user will open up. to connect. A SQL You are now connected to Oracle as an ordinary user, and you can complete your classwork, or even just experiment. If you want to create another user, just connect as “system”, and do what we did to create user “student”. Have fun! Congratulations! You are ready to work in Oracle! Page 42 of 76 Appendix A: Troubleshooting Installation and Usage Issues I Forgot My Password If you forget your password, do not panic, because you can log in a different way and change your password. Your access to the computer onto which you installed Oracle means that you are able to login to Oracle as the SYS user without a password. Your privileges are akin to a master DBA in a production environment. Because you typically install and use your own database locally in this course, security is not usually an issue for your student database. For class databases it is recommended to use an easily memorized password. This method uses the operating system authentication, which must be enabled for this to work. We will login to Oracle using the SYSDBA account for which operating system authentication is turned on by default for operating system users in the “dba” user group. Users with the administrator privilege on MSWindows are usually in the dba user group. This solution depends upon having SQLPlus or another comparable Oracle client installed on the machine. If you are running on a Microsoft Windows platform and you are logged onto the machine as a user in the "dba" operating system user group, which Oracle creates, you don't need to specify a password to connect as sysdba. Open the DOS (Command line) prompt and run "sqlplus / as sysdba". SQLPlus should be launched in the command line interface, connected to Oracle as SYSDBA. You can then change the SYSTEM password with the command: ALTER USER SYSTEM IDENTIFIED BY ; Where is replaced by the new password that you wish to give to the SYSTEM user. For a class installation choose something memorable, and write it down or store it on your computer. Clearly, in a production environment, the password would be carefully chosen to be resistant to attacks, and the password would be carefully protected. After you have changed the SYSTEM password to something that you know, you can connect as SYSTEM, and do anything that you wish, including changing any passwords. There are similar ways of connecting to Oracle if you know the root password on UNIX family platforms. Oracle SQL Developer Won’t Launch in Windows 10 If you have followed the installation instructions for SQL Developer but receive the following error, "Unable to launch the Java Virtual Machine. Located at path: …”, there is a quick fix as outlined below. 1. After unzipping the SQL Developer zip file, go to your sqldeveloper folder in File Explorer. 2. Within that folder, go to the subdirectory “jdk\jre\bin”. 3. Copy the file "msvcr100.dll" within that subdirectory. 4. Go back up to the sqldeveloper folder. Page 43 of 76 5. Within that folder, go to the subdirectory “sqldeveloper\bin” (yes, the initial subdirectory has the same name as the parent folder). 6. Paste the "msvcr100.dll" file you had copied in step #3. You should now be able to launch SQL Developer. If the link is still active, visit https://community.oracle.com/thread/2323010?start=0&tstart=0 for additional details. My Oracle Listeners Are Refusing Connections If you attempt to establish a connection to your database, and the database client reports the following error, “Listener refused the connection with the following error: ORA‐12505”, this section may help you resolve the issue. The error looks similar to the following in Oracle SQL Developer: You may also see this issue while attempting an ODBC connection, as follows: Page 44 of 76 There are two common causes for this error. The first cause is that one or more required Oracle services are not running. This cause is relatively simple to resolve, and should be resolved before attempting to resolve the second cause. The second cause is that the listener may be incorrectly configured. Correcting the second cause is a little more involved and requires updating configuration files. Cause 1: Oracle Services not started You will first want to verify that all required Oracle services are running. 1. Go to Start‐>Control Panel‐>Administrative Tools‐> Services 2. Scroll down to the services that begin with the word “Oracle”. Your screen should look something like this: Make sure that the following services have a status of “Started”: OracleOraDB11g_home1TNSListener OracleServiceXXXX, where XXXX is the Oracle SID you chose during the Oracle installation. If either or both of these services are not running, right click on them and select “Start” from the context menu. Starting the services may be all that you need to do in order to resolve the error. Cause 2: Incorrect Listener Configuration Page 45 of 76 If you have verified that your required Oracle services are started, and you continue to see the “ORA‐12505” error, your listener may be incorrectly configured. The following steps may help you resolve the issue. 1. Update the tnsnames.org configuration file. a. Using Windows Explorer, go to this directory: C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN Note that your directory may vary slightly, depending upon the version of Oracle you are installing, and upon the installation configuration. b. Open the tnsnames.ora file with a text editor, such as notepad or notepad++. When you first open the file, it will look similar to the following: First, completely delete the two lines associated with the LISTENER_XXXX construct. Next, change the word “SERVICE_NAME” to “SID”, and type in your SID after the equal sign. After the changes are applied, your file should look similar to the following: Page 46 of 76 c. Now save the file with the changes. If Windows security will not let you save it as is, then save a copy to the desktop with the same name, then drag the file to the folder to replace the original. Windows will confirm this action with security dialog, which you should approve. 2. Update the listener.ora configuration file. a. Using Windows Explorer, go to this directory: C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN Note that your directory may vary slightly, depending upon the version of Oracle you are installing, and upon the installation configuration. b. Open the listener.ora file with a text editor, such as notepad or notepad++. When you open the file, it will look similar to the following: c. If the machine name is not “localhost” and you are connecting locally to your machine, changing it to “localhost” may help resolve your connection issue. For example, if you see another name such as “HOST = My‐PC”, then you can change it to “Host = localhost” to help resolve your connection issue. d. The file has a missing line that you will add to the end of the file. At the end of the file, add the following line: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = C:\oracle\product\11.2.0\db\dbhome_1)(SID_NAME = LUMP))) Page 47 of 76 Note that if your Oracle installation path differs from the above ORACLE_HOME path, you will need to enter the correct path for your installation. e. After updating the listener.ora file, it will look similar to the following: f. Now save the file with the changes. If Windows security will not let you save it as is, then save a copy to the desktop with the same name, then drag the file to the folder to replace the original. Windows will confirm this action with security dialog, which you should approve. 3. Restart the listener service. a. Go to Start‐>Control Panel‐>Administrative Tools‐>Services b. Scroll down to Oracle services. c. Right click on OracleOraDB11g_home1TNSListener and select “Restart” from the context menu. 4. Ensure that the listener is listening properly by running the lsnrctl command on the command line. a. Go to Start‐>All Programs‐>Accessories‐>Command Prompt Note to Windows 7 users. Make sure to run Command Prompt “As Administrator”. To do so, right click on the Command Prompt icon and choose “Run as Administrator”. b. On the command line, type the command lsnrctl status LISTENER. If your listener is listening correctly, you should see a screen similar to the following: Page 48 of 76 Note the line that states that there is 1 instance running. c. If you see the line that there is 1 instance running, then you should now be able to successfully connect to Oracle using your database client. My Installation Complains that I do not Have a Static IP Many desktops and laptops dynamically obtain their IP address through DHCP, and if so, you may need to install the Microsoft Loopback Adapter with a static IP address. First, check if the Microsoft Loopback adapter is installed. On XP, from the Start menu, choose the Control Panel, then Administrative Tools, then Computer Management then Device Manager. A dialog box similar to the following should appear. Page 49 of 76 If the Loopback adapter is installed, it will appear there. If it is not installed then you need to install it. If it is installed, right click on Microsoft Loopback Adapter, and check that it is enabled. The right‐click menu should include disable. If it includes enable, then you need to click on enable to enable the adapter. You can also do this through the Properties popup. If the Loopback adapter does not appear in the dialog, install it by selecting Add Hardware in the Control Panel. The following dialog will appear: Page 50 of 76 Select the Yes radio button and then Next. You will see a list of hardware already installed on your computer. Scroll down to the bottom of the list and select Add a new hardware device, and then click Next. Page 51 of 76 You will see a list of hardware already installed on your computer. Scroll down to the bottom of the list and select Add a new hardware device, and then click Next. You will see the following screen. Select Search for and install… and click Next. Select Network adapters then Next Page 52 of 76 Page 53 of 76 Check Control Panel‐>Network Connections. If the Loopback Adapter still does not say ‘Connected’ then do the following steps. Page 54 of 76

Oracle Installation Guide - Chapter 3

Oracle Installation Guide - Chapter 3 - Page 3

Oracle Installation Guide - Chapter 3 - Page 4

Oracle Installation Guide - Chapter 3 - Page 5

Oracle Installation Guide - Chapter 3 - Page 6

Oracle Installation Guide - Chapter 3 - Page 7

Oracle Installation Guide - Chapter 3 - Page 8

Oracle Installation Guide - Chapter 3 - Page 9

Oracle Installation Guide - Chapter 3 - Page 10

Oracle Installation Guide - Chapter 3 - Page 11

Oracle Installation Guide - Chapter 3 - Page 12

Oracle Installation Guide - Chapter 3 - Page 13

Oracle Installation Guide - Chapter 3 - Page 14

Oracle Installation Guide - Chapter 3 - Page 15

Oracle Installation Guide - Chapter 3 - Page 16

Oracle Installation Guide - Chapter 3 - Page 17

of 17

Description

Free up your schedule!

Take 5 seconds to unlock