Oracle developer day VM : How to use Pre-Built Oracle database for learning and testing

- Original Topic: http://praitheesh.blogspot.com/2013/10/oracle-developer-day-vm-how-to-use-pre.html

Oracle developer day pre-installed virtual machine is very useful to finish your homework, teach yourself about Oracle database, or get ready for that job interview in SQL / PL SQL.

If you want the Oracle database for testing purpose or if you need mobility database just you can export your application database and import into this pre-installed oracle VM, It is very quick and easy to setup. Here i'm explain how i have import our application database into this VM from scratch.

Oracle provide the pre-Build oracle installed VM for testing and learning purpose. 

1. Download and install Oracle Virtual Box 

https://www.virtualbox.org/wiki/Downloads

2. Download Oracle Developer Day pre-installed VM

• for Oracle Database 11gr2 download this file:

http://download.oracle.com/otn/other/virtualbox/dd/Oracle_Developer_Day.ova

This virtual machine contains:

• Oracle Linux 5
• Oracle Database 11g Release 2 Enterprise Edition
• Oracle TimesTen In-Memory Database Cache
• Oracle XML DB
• Oracle SQL Developer
• Oracle SQL Developer Data Modeler
• Oracle Application Express
• Oracle JDeveloper
• Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

• for Oracle Database 12cr1 download this file:

http://download.oracle.com/otn/other/virtualbox/dd/Oracle_DB_Developer_VM_new.ova

- This virtual machine contains: 

Oracle Linux 6.5
 Oracle Database 12c Release 1 Enterprise Edition
 Oracle XML DB
 Oracle SQL Developer
 Oracle SQL Developer Data Modeler
 Oracle Application Express
 Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

3. Import downloaded ova file into Virtual Box

Click the "Import Appliance.." to import the downloaded ova file

Select the downloaded ova file

Click the check box as Highlighted

Importing Appliance..

Imported.

I have selected the network setting as "Host only Adapter" because i wanted to use the database only within the hosted PC. So i can connect the database from PC which i installed the virtualbox. If you need to access the database from other network you might need to select "Bridge Connection"

Once you finished the Vertualbox installation you can notice new network adapter to connect virtual machines. This will act as gatway between your PC and all virtual mechines.

Check the virtual adapter's setting  for more understanding about network connections

Start the virtual machine and login as oracle , password also "oracle"

configuration parameters.

4. Oracle TNS tnsnames.ora file has configurations for pre-installed database , below is the terminal output

[oracle@localhost ~]$ cat /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
TTORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
[oracle@localhost ~]$

5. Login into oracle database as sysdba then create new user with DBA privileges and normal user to import the database dump.The default password for all accounts is 'oracle'.So you can connect as SYS, HR with 'oracle' as the password. The Linux password are all ‘oracle’ too, so you can login as ‘root’ or as ‘oracle’ in the Linux desktop. Below are the terminal outputs to create the schema / user

[oracle@localhost ~]$ sqlplus 'sys as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 24 22:44:58 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE USER oracle_dba IDENTIFIED BY password$1;

User created.

SQL> GRANT CONNECT TO oracle_dba;

Grant succeeded.

SQL> GRANT DBA TO oracle_dba;

Grant succeeded.

SQL>; CREATE USER SNSDIA IDENTIFIED BY password$1;

User created.

SQL> GRANT CONNECT TO SNSDIA;

Grant succeeded.

SQL>ALTER USER SNSDIA QUOTA 100M ON USERS;

User altered.

6. Import the data dump into newly created schema / user 

[oracle@localhost ~]$ imp oracle_dba@TTORCL file=/home/oracle/Desktop/exp_xxxxx.dmp LOG=xxxxx_import.log FROMUSER=abc TOUSER=acb ignore=Y;

7. Connect to newly created schema from host PC using sql developer

Some information about what you got in these virtual machine and it's contents:

http://docs.oracle.com/cd/E11882_01/server.112/e10831/toc.htm

- list all schemas in database 


SQL> select username,account_status,default_tablespace from dba_users;

- list objects inside [SH] schema for example

SQL> SELECT table_name  from all_tables where owner = 'SH';

TIMES

PRODUCTS

CHANNELS

PROMOTIONS

CUSTOMERS

COUNTRIES

SUPPLEMENTARY_DEMOGRAPHICS

CAL_MONTH_SALES_MV

FWEEK_PSCAT_SALES_MV

DR$SUP_TEXT_IDX$I

DR$SUP_TEXT_IDX$R

SALES_TRANSACTIONS_EXT

DR$SUP_TEXT_IDX$N

DR$SUP_TEXT_IDX$K

COSTS

SALES

DIMENSION_EXCEPTIONS

17 rows selected.

- Sample Schema Diagrams

HR and OE Schemas

PM Schema

SH Schema