Creating an Oracle Autonomous Database
In earlier articles I showed how to install Apache and PHP onto an OCI instance. However, any real website is going to need access to data! In this article I will show you the basics of creating an Autonomous Database that can be used by the Apache/PHP web server. In the next article, I will show you how to connect your PHP pages to the database to read the data.
Prerequisites
Getting access to your own instance of OCI is simple and free. You can register for a free trial at https://cloud.oracle.com. You will be prompted for a valid credit card, but the card will not be charged at the end of the trial unless you explicitly opt in. The free trial is limited to about 30 days or $300 worth of charges, whichever comes first. In my experience, for a single developer who is learning OCI, this is more than enough.
Once you have created your OCI account, login and you will be at the landing screen, shown below:
Click on the main menu icon and select Autonomous Transaction Processing.
Double check the current compartment. You can create the database in any compartment, but I’m selecting the Web compartment that I created earlier for the Ubuntu web server instance. Press the Create Autonomous Database button.
Name the database as shown in the following image. Select the Transaction Processing and Shared Infrastructure options.
You may need to scroll down a bit to see the next portion of the page. Keep the OCPU count (Oracle CPU) at 1 along with the Storage (TB) size. Specify your admin password and be sure to remember it! Scroll down to the next part of the page when ready.
Finally, select the Allow secure access from everywhere option because we will do some of our interaction with the database from our desktop/laptop computer in a bit. Press the Create Autonomous Database button to create the database.
After a second, the main page for your new database will appear. Please wait a minute for the database provisioning process to complete and for the main graphic to turn green, indicating that your new database is ready for use.
Connecting Using Oracle SQL Developer
Now that we have a database, we will want to connect to it as an administrator using a desktop application, like SQL Developer. In a future article, I will show you how to also connect to the database from your PHP website, but for now, let's stay focused on the developer/DBA experience.
Download and Install SQL Developer
If you don’t already have Oracle SQL Developer installed on your local machine, you can download it from: https://www.oracle.com/database/technologies/appdev/sql-developer.html. Be sure to download and install the latest version.
Download Your Credentials File
Click on the DB Connection button to start the download process.
A new window appears. Select the Instance Wallet as the Wallet Type and press the Download Wallet button.
You will be prompted to provide a password for the wallet itself. Use a secure password and make a note of it. Press the Download button to download the file named Wallet_WebDB.zip.
Configure SQL Developer
Now you are ready to create a connection in SQL Developer to your Autonomous Database. In SQL Developer, click on the green plus icon and select New Database Connection.
Locate the Wallet_WebDB.zip file that you downloaded moments ago. Select it and press the Open button.
The name should automatically change to WebDB. Enter admin as the user name and enter the admin password that you created when you created the admin account. Keep the Role field as default. Press the Test button
If the test succeeds (see the Status : Success message in the screenshot above) press the Connect button to close the dialog window. If the test does not succeed, you have likely entered the wrong password or changed the role from default. Double check your settings and try again until the test succeeds.
Your WebDB connection should now appear in the list of connections. Right-click on the WebDB connection and select Open SQL Worksheet from the popup menu.
This will open a SQL worksheet where we can enter some commands to create our products table and populate it with some data.
Now we need to create some data in the database for us to access. In the SQL script below, we will create a product table with 3 rows of data. Copy and paste the code below into your WebDB worksheet.
CREATE TABLE product
(
id NUMBER(10) NOT NULL,
name VARCHAR2(64) NOT NULL,
category VARCHAR2(128) NOT NULL,
price NUMBER(8,2) NOT NULL,
description VARCHAR2(1024),
imageURL VARCHAR2(512) NOT NULL,
CONSTRAINT product_pk PRIMARY KEY (id)
);CREATE SEQUENCE product_id_seq
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
NOCACHE;
INSERT INTO product(id, name, category, price, imageurl) VALUES (product_id_seq.NEXTVAL, 'Product 1', 'Art', 19.99, './images/product1.png');
INSERT INTO product(id, name, category, price, imageurl) VALUES (product_id_seq.NEXTVAL, 'Product 2', 'Art', 21.99, './images/product2.png');
INSERT INTO product(id, name, category, price, imageurl) VALUES (product_id_seq.NEXTVAL, 'Product n', 'Art', 27.99, './images/productn.png');
commit;select * from product;
Press the Run Script button (or the F5 key on your keyboard) to execute all of the commands in the script. I’ve highlighted the button in the screenshot below.
Your Script Output tab at the bottom of the SQL Developer window should show successful completion.
Conclusion
That’s it for this article. Obviously our database is super simple, but we needed to create something for our PHP website to connect with. That’s the topic of the next article so stay tuned and thanks for reading!
Other Reading
Getting Started with Autonomous Data Warehouse.
