Assignments - Oracle Architecture

 LAB - Exercise on ORACLE ARCHITECTURE and INSTALLATION

INSTALLATION

• 19c silent mode installation
• tar ball installation with inventory addition
• database creation using dbca in silent mode
• xming installation into putty

DB CREATION

TASK -1

1)create a pfile with below parameters and start the database to nomount stage

sga_max_size=800m

Db_name=test

once the database is in nomount stage create a database test

- Which file will be executed when you give create database

- Check the BG process running from linux.

- Execute and explain the below packages

• Catalog.sql
• Catproc.sql
• Utlrp.sql

Check all the packages ran successfully?

change the database to OFA(oracle flexible architecture)

Now change all files from OFA to ORADATA as below

/oradata/test/control/control1.ctl

/oradata/test/log/log1.log

/oradata/test/log/log2.log

/oradata/test/data/system.dbf

/oradata/test/data/sysaux.dbf

/oradata/test/data/undo01.dbf

/oradata/test/data/temp01.dbf

/oradata/test/data/data.dbf

/oradata/test/data/index.dbf

/oradata/archive/

Make sure Database is running in Spfile.

Multiplex the control file as shown below

/oradata/test/control/control1.ctl

/oradata/test/control/control2.ctl

/oradata/test/control/control3.ctl

Increase the logfile as 220Mb and create as shown below

Group1

/oradata/test/log/log11.log

/oradata/test/log/log12.log

/oradata/test/log/log13.log

Group2

/oradata/test/log/log21.log

/oradata/test/log/log22.log

/oradata/test/log/log23.log

Group3

/oradata/test/log/log31.log

/oradata/test/log/log32.log

/oradata/test/log/log33.log

===================================================================================

TASK 2 - TABLESPACE

===================================================================================

Create a Tablespace as USER_DATA having two Datafiles as

/oradata/test/data/user_data01.dbf

/oradata/test/data/user_data02.dbf

create a table(only structure) AO using scott user (using CTAS method from all_objects)

and table should be in tablespace USER_DATA.

insert data from all_objects but data should be available only at user_data02 and

should not store in to user_data01.dbf. Also prove that data is stored

only in user_Data02.dbf.

Rename the user_data02 to /oradata/test/data/user_data03.dbf

move the location of

/oradata/test/data/data_user01 to /home/oracle/data_user01.dbf(online mode)

insert data from all_objects into table, make sure data goes into both datafiles.

drop the datafile /oradata/test/data/data_user03.dbf

select all the data from the AO table.

verify the error by retrieving data from table.

drop the tablespace user_data.

move the datafile SYSAUX to another location(online)

move the datafile SYSTEM to another location (online)

===================================================================================

TASK 3 - UNDO TABLESPACE & TEMP Tablespace

===================================================================================

Demonstrate the ORA-0555 error

Rename the undo tablespace to undo01 tbs

Check if undo tablespace can be brought offline

rename the temp tablespace to temp01 tablespace

get the statistics of database(types – fixed table, table, schema,data dictionary)

===================================================================================

TASK 4 - FRAGMENTATION

===================================================================================

create a table like AO from all_objects on a newly created tablespace (eg. USER_DATA01)

alter the table and add an extra column as “aoid”

create an unique index and primary key on column “aoid”, check if possible and explain.

create composite index on (owner, object_id)

create a sequence to generate values for the column “aoid”

all other data to be inserted from all_objects(upto 1lak records), duplicate it with

sql> insert into ao select ... from ao;

check

table size

index size

number of extents

number of blocks of tables and indexes

delete from table where rownum < 50000

Again check

table size

index size

number of extents

number of blocks of tables and indexes

The size will remain the same, explain why?

check the fragmentation for table, index using our script.

Repeat above steps for each defragment method and print it in table format.

Defrag method ---- Object-type -- index status -- original size -- fragmented size- after defrag size

1. alter table .. move

2. alter table shrink

3. online redefinition

4. exp/imp

5. CTAS

===================================================================================

TASK 5 - FLASHBACK

===================================================================================

create table tab01 from all_objects and insert data, commit the same

demonstrate as of query

drop the table

restore the table

===================================================================================

TASK 6 - ROLES AND PROFILES

===================================================================================

create a user(without any role) with password tesdb123 and the user should belong

to user_data tablespace

connect to the database as user and create a table tab01 as select * from all_objects.

check and rectify the error

Try the below password limits:

create a user with a password expiration limit in 2 days

password should have one capital letter, number, special character

should not allow the old password to be reused and account to be locked after 3

wrong attempts.

password expiration, recreate password

===================================================================================

TOPIC 7- SCHEDULER

===================================================================================

create a job to gather the stat for schema - SCOTT every one hour

create a job to find the fragmentation every week saturday at 3:00 am.

More practical scenario questions in our repository.