U.B. Database on Demand
Getting Started Guide
Enterprise Infrastructure Services Stephen G. Comings
Manager, Systems Software
Document version 1.2 Updated 09/01/2015
CIT Enterprise Infrastructure Services 2
Contents
Requesting Access ... 3
Connecting to the Portal ... 3
Creating a Database ... 4
Connecting to Your New Database ... 7
Back Up Your Database ... 9
Creating User Accounts ... 11
CIT Enterprise Infrastructure Services 3
Requesting Access
Request access to the cloud portal by going to following URL:
https://ubithelp.my.salesforce.com/
Choose “Request a Service”, then “Other or Not Known”, and “General EIS Request”.
For “Service name or machine name” select “Database – General”, and in the “Description” enter the text, “Please provide access to the AOS DB Cloud environment”.
If you are setting up a class, please provide the following additional information:
Professor’s Name Professor’s UBITname
Professor’s Contact Info (phone number, email address) Class Name (i.e. MGS, CSE, GEO)
Class ID
If the professor is not going to be the database administrator (DBA) then please also provide:
DBA’s Name DBA’s UBITname
DBA’s Contact Info (phone number, email address)
Connecting to the Portal
Use a web browser to connect to the cloud portal web site. Note that you will need to be accessing the portal from a UB network (use UB VPN if off campus).
https://dbcloud.acsu.buffalo.edu/em
Log in using your UBit account name, with the password provided to you. Note this is not your regular UBit password, but one provided to you by the CIT EIS Database Team (see “Requesting Access”, above). Your department may have multiple people, each with their own account, who can log into the portal and (via assigned roles) perform certain tasks. Or your department may have a single shared account for the portal.
CIT Enterprise Infrastructure Services 4
Creating a Database
Select “Databases” from the “Manage” pull-down menu.
CIT Enterprise Infrastructure Services 5 Select the Service Template for your database (there might only be one choice). Then click on the “Select” button.
Enter information for your database. The “Zone” will likely not be changeable. Change the schedule (when the database will be created) if you wish. Specify the username and password you want for your database – please use “MASTER” as the username for best results. Enter a Database Service Name (default is “Service”). Enter an end date (Duration) for your database. Then click on “Submit”.
Note that you must specify an end date (Duration) for your database of no more than 12 months from the present day. Otherwise you will receive a pop-up error as shown below.
CIT Enterprise Infrastructure Services 6 Once you have clicked on the “Submit” button you will see your database being created in the
“Requests” panel as shown below.
In about 15 minutes you should see the database has been created (see screen clip below, note “Success” in the Status field).
CIT Enterprise Infrastructure Services 7
Connecting to Your New Database
Make note of your database’s connection information. First click on the database name to see the details about the database.
Find the HOST, PORT, and SERVICE_NAME in the information displayed (highlighted in blue below). These values are created by the system.
CIT Enterprise Infrastructure Services 8 Connect to your database using SQL Plus, Oracle SQL Developer, Toad, or similar tools. Note you will not need to run the UB VPN or departmental VPN to make the connection.
For example, with Oracle SQL Developer (shown below), create a new connection similar to the following. Enter the Connection Name you would like to use, the username and password you defined when you created the database, the Hostname, Port, and Service name.
At this point you should be able to connect to your database and execute SQL statements. More information about Oracle “connection strings” is available on the web, for example:http://www.connectionstrings.com/
CIT Enterprise Infrastructure Services 9
Back Up Your Database
Set up a backup for your database. Click on your database (underlined in the Database Service Instances pane) to open the Database Instance web page. (Note in this example the database is cse00000.)
CIT Enterprise Infrastructure Services 10 In the Database Instance web page, find the “Backup and Restore” pane and select “Schedule Daily Backup”
CIT Enterprise Infrastructure Services 11 Enter a start date and time (or choose the default). Avoid times when your database is likely to be in use.
Click on the “Schedule” button, and your daily backups will be scheduled.
Creating User Accounts
User accounts may be created by executing SQL commands in (for example) the Oracle SQL Developer tool.
Depending on how you will use your database, you might want to set up multiple tablespaces – for example if you are a professor running multiple classes. Execute a SQL statement similar to the following.
CREATE TABLESPACE "CLASS01" ONLINE;
You can create user accounts using SQL commands similar to the following. create user student01 identified by password_here default tablespace CLASS01
temporary tablespace TEMP quota unlimited on CLASS01; grant connect to student01;
grant CONNECT_9I to student01;
Granting the role CONNECT_9I will allow the user to connect to the database, alter session, create views, tables, synonyms, sessions and sequences.
CIT Enterprise Infrastructure Services 12
Additional Information
Oracle documentation is available from the web portal. Click on “Help” and then “Documentation Library”.
