Lab 1 – Oracle Db Creation – Simple Manual

Preparing Environment

  • Install Virtial BOX (Software from our Portal)
  • Install Linux OS (Software from our Portal)
  • Install Software (silent mode) under /u01/app/oracle/product/12.2
  • Add additional mountpoint as /u02 with size 40G , with ext3 filesystem

Configure oracle database with DBCA

  • Configure putty to connect to the Linux server
  • Create database using dbca from putty (Make sure you use Xming for GUI access)
  • create a cron script to monitor alert log, space on file system, log rotate
  • create a corn script to check DB and listener availability

Database TASK 1

Note :Make sure you have atleast 40gb free space in /u02
vi $ORACLE_HOME/dbs/initgopi.ora

$export ORACLE_SID=gopi

$sqlplus / as sysdba

sql> startup nomount

sql> create database;
Note: open one more terminal and monitor the alertlog using tail -f command.
sql> @?/rdbms/admin/catalog.sql
sql> @?/rdbms/admin/catproc.sql
sql> @?/rdbms/admin/utlrp.sql

Database TASK 2

create a folder /u02/oradata/gopi/

Move your controlfile to /u02/oradata/gopi/ as controlgopi1.ctl and multiplex to controlgopi2.ctl,controlgopi3.ctl

Database TASK 3

create a folder /u02/oradata/gopi/

Move your logfile to /u02/oradata/gopi/ as log1gopi1.log, log1gopi2.log and multiplex to one more group as log2gopi1.log, log2gopi2.log and increase the size to 20m;

Database TASK 4

create a folder /u02/oradata/gopi/

create temporary tablespace TEMP and undo tablespace as UNDOTBS under /u02/oradata/gopi/ as temp01.dbf and undotbs01.dbf

Database TASK 5

create a tablepace USERS under /u02/oradata/gopi/ as users01.dbf with size 10m; –> assign (connect , reqource roles)
create a tablepace USER_DATA under /u02/oradata/gopi/ as users_data01.dbf with size 10m; —> assign (connect , reqource roles)

Database TASK 6

Create the sample user SCOTT using @?/rdbms/admin/utlsampl.sql
Also, create a user expuser , with passwrod expuser , default tablespace USER_DATA and temporary tablepace TEMP;

Database TASK 7

while connecting scott, you will get a error for pupbld. , to solve that connect to system/ execute , @?/sqlplus/admin/pupbld.sql

Database TASK 8

move all the datafiles to /u02/oradata/gopi/ (use alter database rename file ….)

USERS,USER_DATA, should be move to the location when DB is online
SYSTEM/SYSAUX in mount state

Database TASK 9

connect to expuser and create the below table in USERS Tablespace;
create the table AO as select * from all_objects;
execute the below query 10 times;
insert into AO select * from AO;
Solve the errors that you face when inserting.

Database TASK 10

drop the database , using drop database command (Note: user restrict exclusive options of startup command)