ADVANCED DATABASE ADMINISTRATION

LAB
PAPER CODE (ETIT-459)

Submitted To: - Submitted By: -

Dr. Shikha Gupta Name: Aman
E. No.: 01314803120
Group: 7I1

MAHARAJA AGRASEN INSITUTE OF TECHNOLOGY,

DELHI -110086

Affiliated To

GURU GOBIND SINGH

INDRAPRASTHA UNIVERSITY
 ADVANCED DATABASE ADMINISTRATION LAB
PRACTICAL RECORD

Paper code: ETIT - 459

Name of student: Aman
University roll no: 01314803120
Branch: IT
Group: 7I1

PRACTICAL DETAILS:

S.No. Experiment Name Date Signature

1. Introduction to Oracle Architecture

2. Write a program to create a database in Oracle

11G/MySQL

3. Write a program to create new tablespace in Oracle

11G/MySQL

4. Write a program to create temporary tablespace

in Oracle 11G/MySQL

5. Write a program to manage redo log file in

Oracle 11G/MySQL

6. Write a program to manage control Files in

Oracle 11G/MySQL

7. Write a program to Managing the undo Tablespace

in Oracle 11G/MySQL

8. Write a program to migrate data from MySQL to

Oracle using SQL Loader tool

9. Write a program to Import Utility Usage in

Oracle 11G/MySQL

10. Write a program to Use the FLASHBACK

Query Feature in Oracle 11G/MySQL
 EXPERIMENT - 1
AIM: Introduction to Oracle Architecture
TOOLS REQUIRED: Oracle 10g DBMS, MySQL
THEORY: An Oracle database is a collection of data treated as a unit. This database’s main
aim is to store and retrieve related information. The problems of information management are
solved by a database server. In a multiuser environment, so that many users can concurrently
access the same data a server is used. This helps in delivering high performance. Prevention of
unauthorized access and failure recovery are accomplished using a database server.
Enterprise Grid Computing was done initially on the Oracle database. It is the most cost
effective and flexible way to manage information and applications. Large pools of industry-
standard, modular storage and servers are created by Enterprise Grid Computing. Each new
system can be rapidly provisioned from the pool of components using this architecture.
Capacity can be easily added or reallocated from the resource pools as needed so there is no
need for peak workloads.
The Oracle database has both logical as well as physical structures. As both these structures are
separate, the physical storage of data can be managed without affecting access to logical
structures.
Overview of Oracle Grid Architecture
Large numbers of servers, storage and networks are pooled into a flexible on demand
computing resource for enterprise computing needs by the Oracle Grid Architecture.
Continually analysing the demand for resources and adjusting supply accordingly is done by
the grid computing infrastructure.
For Example, one can run different applications on a grid of several linked database servers. To
handle the increased demand, when reports are due at the end of the month, the database
administrator can provision more servers.
Sophisticated workload management is used by Grid Computing to make it possible for
applications to share resources across many servers. Resources within a location can be
dynamically provisioned and data processing capacity can be added or removed on demand.
New business processes can be created by integrating applications done by web services.
Difference between a cluster and a grid
Grid Infrastructure can be created by Clustering. Simple clusters have static resources for
specific applications by specific owners. Multiple clusters form Grids which are dynamic
resource pools shareable among many different applications and users. A grid does not assume
that all the servers in the grid are running the same set of applications. Applications can be
scheduled and migrated across servers in the grid. Grids share resources from and among
independent system owners.
The idea of grid computing is computing as a utility at the highest level. In other words, one
must not care where the data resides or what computer processes his request. One should be
able to request information or computation and have it delivered as much as he wants or
whenever he wants. This is like the way electric utilities work, in that one does not know where
the generator is or how the electric grid is wired, he just asks for electricity and he gets it. The
goal is to make computing a utility, a commodity and ubiquitous. So, it is called “The Grid”.
This view of utility computing is a “Client Side” view.
From the “Server Side” or behind the scenes, the grid is about resource allocation, high
availability, and information sharing. The resources are not standing idle while requests are
going un-serviced and all those are getting what they need is ensured by resource allocation.
The information users and applications need are available where and when it is needed is
ensured by information sharing. All the data and computation are guaranteed by high
availability features, just like a utility company always provides electricity.

RESULT: The experiment is successful in explaining the Grid architecture of Oracle.

 EXPERIMENT - 2
AIM: Write a program to create a database in MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL
THEORY: These steps were followed in the order presented. Before creating the database, it
was made sure that the planning was done about the size of the database, number of tablespaces
and redo log files required in the database.

Firstly, it was figured out how many tables were needed to be created in the database and how
much space they will be occupying for the next 1 year or 2. It started with some specific size
and later the size was adjusted according to the requirement.

Layout of the underlying operating system files was planned.

Standard database block size was selected. It was specified at database creation by the
DB_BLOCK_SIZE initialization parameter.

Before creating the Database, specification was noted down

//The examples shown in these steps create an example database my_ica_db

//Let us create a database my_ica_db with the following specification

//Database name and System Identifier
SID=myicadb DB_NAME=myicadb

TABLESPACES
(we have 6 tablespaces in this database, with 1 datafile in each tablespace)

Tablespace Name Datafile Location Size

SYSTEM /u01/oracle/oradata/myica/sys.dbf 500M
USERS /u01/oracle/oradata/myica/usr.dbf 100M
UNDOTBS /u01/oracle/oradata/myica/undo.dbf 100M
TEMP /u01/oracle/oradata/myica/temp.dbf 100M
INDEX_DATA /u01/oracle/oradata/myica/indx.dbf 100M
SYSAUX /u01/oracle/oradata/myica/sysaux.dbf 100M
LOGFILES
(we have 2 log groups in the database)

Logfile Group Member Location Size

GROUP 1 /u01/oracle/oradata/myica/log1.ora 10M
GROUP 2 /u01/oracle/oradata/myica/log2.ora 10M
CONTROL FILE
(We have 1 Control File in the following location)
/u01/oracle/oradata/myica/control.ora PARAMETER FILE
(we have used normal parameter file, later on we switched to SPFile)

/u01/oracle/dbs/initmyicadb.ora

//(remember the parameter file name should of the format init<sid>.ora and it should be
in ORACLE_HOME/dbsdirectory in Unix o/s and ORACLE_HOME/database directory in
windows o/s)

Steps for creating the database

Step 1: logged in in the Oracle account and directories were made for the database.

$ mkdir /u01/oracle/oradata/myica
$ mkdir /u01/oracle/oradata/myica/bdump
$ mkdir /u01/oracle/oradata/myica/udump
$ mkdir /u01/oracle/oradata/myica/recovery

Step 2: Parameter file was created by copying the default template (init.ora) and required
parameters were set.

$ cd /u01/oracle/dbs
$ cp init.ora initmyicadb.ora

Now parameter file was opened and following parameters were set

$ vi initmyicadb.ora

DB_NAME=myicadb DB_BLOCK_SIZE=8192
CONTROL_FILES=/u01/oracle/oradata/myica/control.ora

UNDO_TABLESPACE=undotbs UNDO_MANAGEMENT=AUTO
SGA_TARGET=500M
PGA_AGGREGATE_TARGET=100M LOG_BUFFER=5242880
DB_RECOVERY_FILE_DEST=/u01/oracle/oradata/myica/
recovery DB_RECOVERY_FILE_DEST_SIZE=2G
# The following parameters were required only in 10g or earlier
versions BACKGROUND_DUMP_DEST=/u01/oracle/oradata/myica/bdump
USER_DUMP_DEST=/u01/oracle/oradata/myica/udump

After entering the above parameters the file was saved by pressing "Esc :wq"

Step 3: ORACLE_SID environment variable was set and the instance was started.

$ export ORACLE_SID=myicadb

$ sqlplus
Enter User: / as sysdba SQL>startup nomount

Step 4: Create database command was given. The command to create the database
is SQL> create database myicadb
datafile ‘/u01/oracle/oradata/myica/sys.dbf’ size 500M
sysaux datafile ‘/u01/oracle/oradata/myica/sysaux.dbf’ size 100m undo tablespace undotbs
datafile ‘/u01/oracle/oradata/myica/undo.dbf’ size 100m default temporary tablespace
temp tempfile ‘/u01/oracle/oradata/myica/tmp.dbf’ size 100m logfile
group 1 ‘/u01/oracle/oradata/myica/log1.ora’ size 50m, group 2
‘/u01/oracle/oradata/myica/log2.ora’ size 50m;

After the command was finished, following message was received Database created.
Step 5: After the above command was finished, the database was mounted and opened. Now
additional tablespaces were created
To create USERS tablespace SQL> create tablespace
users datafile ‘/u01/oracle/oradata/myica/usr.dbf’ size
100M;

To create INDEX_DATA

tablespaces SQL>create tablespace

index_data
datafile ‘/u01/oracle/oradata/myica/indx.dbf’ size 100M

Step 6: To populate the database with data dictionaries and to install procedural options
following scripts were executed.

Firstly, CATALOG.SQL script was executed to install data dictionaries

SQL>@/u01/oracle/rdbms/admin/catalog.sql
The above script took several minutes. After the above script was finished CATPROC.SQL
script was executed to install procedural option.

SQL>@/u01/oracle/rdbms/admin/catproc.sql

This script also took several minutes to get completed.

Step 7: Passwords for SYS and SYSTEM account was changed, since the default passwords
change_on_install and manager are known by everybody.

SQL>alter user sys identified by myica; SQL>alter user system identified by myica;
Step 8: Additional user accounts were created. Account SCOTT was created. SQL>create user
scott default tablespace users identified by tiger quota 10M on users;
SQL>grant connect to scott;

Step 9: Database SID was added in listener.ora file and listener process was restarted.

$ cd /u01/oracle/network/admin
$ vi listener.ora LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST=200.200.100.1)(PORT = 1521))
)
) SID_LIST_LISTENER = (SID_LIST
= (SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/oracle) (PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=orcl) (ORACLE_HOME=/u01/oracle)
)
)

#Add these lines in SID_LIST_LISTENER at the bottom of file (SID_DESC

= (SID_NAME=myicadb) (ORACLE_HOME=/u01/oracle)
)

The file was saved by pressing Esc :wq The listener process was restarted.
$ lsnrctl stop
$ lsnrctl start

Step 10: Backup was created of the full database just after creating the database.
OUTPUT:

RESULT: A new database has been created.

 EXPERIMENT - 3
AIM: Write a program to create a tablespace in MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL
THEORY: Using multiple tablespaces provides several advantages, as follows:
Separate user data from data dictionary data to reduce contention among dictionary objects
and schema objects for the same datafiles.
Separate data of one application from the data of another to prevent multiple applications
from being affected if a tablespace must be taken offline.
Store different the datafiles of different tablespaces on different disk drives to reduce I/O
contention.
Take individual tablespaces offline while others remain online, providing better overall
availability.
Creating New Tablespaces
You can create Locally Managed or Dictionary Managed Tablespaces. In prior versions of
Oracle, only Dictionary managed Tablespaces were available but from Oracle version, 8i you
can also create Locally Managed tablespaces. The advantages of locally managed tablespaces
are
Locally managed tablespaces track all extent information in the tablespace itself by using
bitmaps, resulting in the following benefits:
Concurrency and speed of space operations is improved, because space allocations and
deallocations modify locally managed resources (bitmaps stored in header files) rather than
requiring centrally managed resources such as enqueues
Performance is improved, because recursive operations that are sometimes required during
dictionary-managed space allocation are eliminated
To create a locally managed tablespace, give the following command
SQL> CREATE TABLESPACE ica_lmts DATAFILE '/u02/oracle/ica/ica01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size
of 64K.
The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is
managed with extents of uniform size. You can specify that size in the SIZE clause of
UNIFORM. If you omit SIZE, then the default size is 1M. The following example creates
a Locally managed tablespace with uniform extent size of 256K
SQL> CREATE TABLESPACE ica_lmt DATAFILE '/u02/oracle/ica/ica01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
To Create Dictionary Managed Tablespace:
SQL> CREATE TABLESPACE ica_lmt DATAFILE '/u02/oracle/ica/ica01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY;
Bigfile Tablespaces (Introduced in Oracle Ver. 10g)
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile.
Traditional smallfiletablespaces, in contrast, can contain multiple datafiles, but the files
cannot be as large. Bigfile tablespaces can reduce the number of datafiles needed for a
database.
To create a bigfile tablespace give the following command
SQL> CREATE BIGFILE TABLESPACE ica_bigtbs DATAFILE
'/u02/oracle/ica/bigtbs01.dbf' SIZE 50G;

OUTPUT:

RESULT: A new tablespace has been created.

 EXPERIMENT - 4
AIM: Write a program to create a temporary tablespace in MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL
THEORY: Temporary tablespace is used for sorting large tables. Every database should have
one temporary tablespace. A temporary tablespace is usually created at the time of Database
Creation. However temporary tablespace can be created afterwards.
To create temporary tablespace, the command is:
SQL> create temporary tablespace temp tempfile ‘/u01/oracle/data/ica_temp.dbf’ size 100M
extent management local uniform size 5M;
The extent management clause is optional for temporary tablespaces because all temporary
tablespaces are created with locally managed extents of a uniform size. The
AUTOALLOCATE clause is not allowed for temporary tablespaces.
Tablespace Groups
A tablespace group enables a user to consume temporary space from multiple tablespaces. A
tablespace group has the following characteristics:
It contains at least one tablespace. There is no explicit limit on the maximum number of
tablespaces that are contained in a group.
It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
We can specify a tablespace group name wherever a tablespace name would appear when we
assign a default temporary tablespace for the database or a temporary tablespace for a user.
We cannot explicitly create a tablespace group. Rather, it is created implicitly when you
assign the first temporary tablespace to the group. The group is deleted when the last
temporary tablespace it contains is removed from it.
Using a tablespace group, rather than a single temporary tablespace, can alleviate problems
caused where one tablespace is inadequate to hold the results of a sort, particularly on a table
that has many partitions. A tablespace group enables parallel execution servers in a single
parallel operation to use multiple temporary tablespaces.
The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member
tablespaces
Increasing or decreasing the size of a Temporary Tablespace
We used the resize clause to increase or decrease the size of a temporary tablespace. The
following statement resizes a temporary file:
SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
The following statement drops a temporary file and deletes the operating system file:
SQL> ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP
INCLUDING DATAFILES;
STEPS:

1. Creating a Temporary Tablespace Group

We created a tablespace group implicitly when we included the TABLESPACE GROUP
clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement
and the specified tablespace group does not currently exist.
For example, if neither group1 nor group2 exists, then the following statements create those
groups, each of which has only the specified tablespace as a member:
CREATE TEMPORARY TABLESPACE ica_temp2 TEMPFILE
'/u02/oracle/ica/ica_temp.dbf'
SIZE 50M TABLESPACE GROUP group1;
ALTER TABLESPACE ica_temp2 TABLESPACE GROUP group2;
2. Assigning a Tablespace Group as the Default Temporary Tablespace
Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign
a tablespace group as the default temporary tablespace for the database. For example:
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
3. To view information about Temporary Tablespaces and
Tempfiles SQL>select * from dba_temp_files;
SQL>select * from v$tempfile;
To view information about free space in
tempfiles SQL>select * from
V$TEMP_SPACE_HEADER OUTPUT:

RESULT: The experiment has been successful in demonstrating temporary tablespaces.

 EXPERIMENT - 5
AIM: Write a program to manage a redo log file in MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL
THEORY: Oracle database must have at least 2 redo logfile groups. Oracle writes all
statements except, SELECT statement, to the logfiles. This is done because Oracle performs
deferred batch writes i.e., it does write changes to disk per statement instead it performs write
in batches. So, in this case if a user updates a row, Oracle will change the row in
db_buffer_cache and records the statement in the logfile and give the message to the user that
row is updated. The row is not yet written back to the datafile but still it gives the message to
the user that row is updated. After 3 seconds the row is written to the datafile. This is known
as deferred batch writes.
Since Oracle defers writing to the datafile there is chance of power failure or system crash
before the row is written to the disk. That is why Oracle writes the statement in redo logfile
so that in case of power failure or system crash oracle can re-execute the statements next time
when you open the database.
Adding a New Redo Logfile Group
To add a new Redo Logfile group to the database give the following
command SQL>alter database add logfile group 3 ‘/u01/oracle/ica/log3.ora’
size 10M;
Note: You can add groups to a database up to the MAXLOGFILES setting you have
specified at the time of creating the database. If you want to change MAXLOGFILE setting
you have to create a new controlfile.
Adding Members to an existing group
To add new member to an existing group, give the following command
SQL>alter database add logfile member ‘/u01/oracle/ica/log11.ora’ to group
1;
Note: You can add members to a group up to the MAXLOGMEMBERS setting you have
specified at the time of creating the database. If you want to change MAXLOGMEMBERS
setting you have created a new controlfile
Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log
members, one member in one disk and another in second disk, in a database.
Dropping Members from a group
You can drop member from a log group only if the group is having more than one member
and if it is not the current group. If you want to drop members from the current group, force a
log switch or wait so that log switch occurs and another group becomes current. To force a
log switch, give the following command
SQL>alter system switch logfile;
The following command can be used to drop a logfile member
SQL>alter database drop logfile member ‘/u01/oracle/ica/log11.ora’;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S
command to delete the files from disk.
Dropping Logfile Group
Similarly, you can also drop logfile group only if the database is having more than two
groups and if it is not the current group.
SQL>alter database drop logfile group 3;
Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S
command to delete the files from disk.
Resizing Logfiles
You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the
new size and subsequently drop the old logfile group.
Renaming or Relocating Logfiles
To Rename or Relocate Logfiles perform the following steps
For Example, suppose you want to move a logfile from ‘/u01/oracle/ica/log1.ora’ to
‘/u02/oracle/ica/log1.ora’, then do the following
Steps
1. Shutdown the database
SQL>shutdown immediate;
2. Move the logfile from old location to new location using operating system command
$mv /u01/oracle/ica/log1.ora /u02/oracle/ica/log1.ora
3. Start and mount the
database SQL>startup mount
4. Now give the following command to change the location in controlfile
SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;
5. Open the database
SQL>alter database open;
Clearing REDO LOGFILES
A redo log file might become corrupted while the database is open, and ultimately stop
database activity because archiving cannot continue. In this situation the ALTER
DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting
down the database.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
If there are only two log groups
The corrupt redo log file belongs to the current group
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the
statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared redo
logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer recover
from that backup. The database writes a message in the alert log describing the backups from
which you cannot recover
Viewing Information About Logfiles
To See how many logfile groups are there and their status type the following
query. SQL>SELECT * FROM V$LOG;
GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE#
FIRST_TIM

1 1 20605 1048576 1 YES ACTIVE 61515628 21-JUN-07

2 1 20606 1048576 1 NO CURRENT 41517595 21-JUN-07
3 1 20603 1048576 1 YES INACTIVE 31511666 21-JUN-07
4 1 20604 1048576 1 YES INACTIVE 21513647 21-JUN-07
To See how many members are there and where they are located give the following
query SQL>SELECT * FROM V$LOGFILE;
GROUP# STATUS MEMBER

1 /U01/ORACLE/ICA/LOG1.ORA
2 /U01/ORACLE/ICA/LOG2.ORA
OUTPUT:

RESULT: The experiment has successfully demonstrated managing a REDO LOG FILE.
 EXPERIMENT - 6
AIM: Write a program to manage control files in MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL
THEORY: Every Oracle Database has a control file, which is a small binary file that records
the physical structure of the database. The control file includes:
The database names
Names and locations of associated datafiles and redo log files
The timestamp of the database creation
The current log sequence number
Checkpoint information
It is strongly recommended that you multiplex control files i.e., Have at least two control files
one in one hard disk and another one located in another disk, in a database. In this way if
control file becomes corrupt in one disk another copy will be available and you do not have
to do recovery of control file.
You can multiplex control file at the time of creating a database and later also. If you have
not multiplexed control file at the time of creating a database you can do it now by following
given procedure.
Steps to multiplex the control file:
1. Database is being shut down.
SQL>SHUTDOWN
IMMEDIATE;
2. Operating system command is used and the control file is copied from old to new location.
For example:
$ cp /u01/oracle/ica/control.ora /u02/oracle/ica/control.ora
3. Parameter file is opened and new location is
specified.
CONTROL_FILES=/u01/oracle/ica/control.ora
The control file is changed to
CONTROL_FILES=/u01/oracle/ica/control.ora,/u02/oracle/ica/control.ora
4. Database is started
Control files are updated by oracle and the lost file could be recovered from another location.
Database name is being changed:
Created a new control file for changing the name of database.
New control file is being created using following steps:
1. Create control file statement is generated
SQL>alter database backup controlfile to trace;
The CREATE CONTROLFILE statement is being written by oracle in a trace file with any
arbitrary name like ORA23212.TRC which is saved in USER_DUMP_DIRECTORY
2. Latest trace file is being opened in text editor which contains CREATE CONTROLFILE
statement.RESETLOGS and without RESETLOGS statement is being found in trace file.The
one with RESETLOGS is selected and pasted in new file which is called as c.sql.
3. The example is being given to change the database name from ica to prod in c.sql.
CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1
('/u01/oracle/ica/redo01_01.log',
'/u01/oracle/ica/redo01_02.log'),
GROUP 2 ('/u01/oracle/ica/redo02_01.log',
'/u01/oracle/ica/redo02_02.log'),
GROUP 3 ('/u01/oracle/ica/redo03_01.log',
'/u01/oracle/ica/redo03_02.log')
RESETLOGS
DATAFILE '/u01/oracle/ica/system01.dbf' SIZE 3M,
'/u01/oracle/ica/rbs01.dbs' SIZE 5M,
'/u01/oracle/ica/users01.dbs' SIZE 5M,
'/u01/oracle/ica/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
4. Database is started
SQL>STARTUP
NOMOUNT;
5. Now execute c.sql
script SQL>
@/u01/oracle/c.sql
6. Now open the database with RESETLOGS
SQL>ALTER DATABASE OPEN
RESETLOGS;
OUTPUT:

RESULT: Control files are being managed.

 EXPERIMENT - 7
AIM: Write a program to manage undo tablespaces in MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL
THEORY: A method of maintaining information that is used to roll back, or undo, changes
to the database must be there in every oracle database. Such information consists of records
of the actions of transactions, primarily before they are committed. These records are
collectively referred to as undo. Undo records are used to:
When a ROLLBACK statement is issued, transactions are roll backed.
Database is recovered
Read consistency is provided
Using Flashback Query data is analyzed for an earlier point in time.
Using Flashback features logical corruptions are recovered.

Rollback segments to store undo were used in earlier releases of oracle database. Oracle 9i
introduced automatic undo management, which simplifies undo space management by
eliminating the complexities associated with rollback segment management. Use of undo
Tablespace to manage undo rather than rollback segments is strongly recommended by
oracle.

Switching to Automatic Management of Undo Space:

The following parameter must be set to go for automatic management of undo space.

Steps:
1. If an undo Tablespace is not created at the time of creating database the, and
undo Tablespace is created by typing the following command
SQL>create undo tablespace myundo datafile
‘/u01/oracle/ica/undo_tbs.dbf’ size 500M
autoextend ON next 5M ;
Space requirements of the undo tablespace maybe unsure, when the system is first ran in the
production enviornment. In this case, an automatic extension for datafiles of the undo
tablespace can be enabled so that they automatically increase in size when more space is
needed

2. Database is Shutdown and the following parameters in parameter file are set.
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=myundo

3. Database is Started.
Automatic Undo Space Management will be used by Oracle.
Calculating the Space Requirements For Undo Retention
Space requirements can be calculated manually using the following formula:
UndoSpace = UR * UPS + overhead
where:
UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds. This value should take into consideration long-
running queries and any flashback requirements.
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is
100 undo blocks for each second, with a 8K block size, the required undo space is computed
as follows:
(3 * 3600 * 100 * 8K) = 8.24GBs
To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the
following statement
SQL> Select * from V$UNDOSTAT;

Altering UNDO Tablespace:

If the Undo tablespace is full, you can resize existing datafiles or add new datafiles to it
The following example extends an existing datafile
SQL> alter database datafile ‘/u01/oracle/ica/undo_tbs.dbf’ resize 700M
The following example adds a new datafile to undo tablespace
SQL> ALTER TABLESPACE myundo ADD DATAFILE '/u01/oracle/ica/undo02.dbf' SIZE
200M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace:

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example
drops the undo tablespace undotbs_01:
SQL> DROP TABLESPACE myundo;
An undo tablespace can only be dropped if it is not currently used by any instance. If the
undo tablespace contains any outstanding transactions (for example, a transaction died but
has not yet been recovered), the DROP TABLESPACE statement fails.

Switching Undo Tablespaces:

One undo tablespace canbe switched to another. Because the UNDO_TABLESPACE
initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be
used to assign a new undo tablespace.
The following statement switches to a new undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = myundo2;
Assuming myundo is the current undo tablespace, after this command successfully executes,
the instance uses myundo2 in place of myundo as its undo tablespace.

Viewing Information about Undo Tablespace:

To view statistics for tuning undo tablespace query the following dictionary
SQL>select * from v$undostat;
To see how many active Transactions are there and to see undo segment information give the
following command
SQL>select * from v$transaction;
To see the sizes of extents in the undo tablespace give the following query
SQL>select * from DBA_UNDO_EXTENTS;
OUTPUT:

RESULT: The experiment has been successful in demonstrating UNDO Tablespace.

 EXPERIMENT - 8
AIM: Write a program to migrate data from MySQL to Oracle using SQL Loader tool.
TOOLS REQUIRED: Oracle 10g DBMS, MySQL

THEORY: Example of a table "Orders" in MySQL running in Windows:

This table has some 1000 rows and we need to load these rows into a table in Oracle running
under Linux O/s.

MySQL is an open-source relational database management system (RDBMS). The MySQL

development project has made its source code available under the terms of the GNU General
Public License, as well as under a variety of proprietary agreements. MySQL was owned and
sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by
Oracle Corporation. For proprietary use, several paid editions are available, and offer
additional functionality.
Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is a multi-
model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for running online transaction processing (OLTP), data
warehousing (DW) and mixed (OLTP & DW) database workloads. The latest generation,
Oracle Database 18c, is available on-prem, on-Cloud, or in a hybrid-Cloud environment. 18c
may also be deployed on Oracle Engineered Systems (e.g., Exadata) on-prem, on Oracle
(public) Cloud or (private) Cloud at Customer. At Openworld 2017 in San Francisco,
Executive Chairman of the Board and CTO, Larry Ellison announced the next database
generation, Oracle Autonomous Database.
Procedure:

1: First the rows from MySQL table to a CSV or any delimited file were exorted. To export the
rows HeidiSQL (database tool) was used

2: Rows were exported to a CSV file using HeidiSQL, by the undergiven steps:

3: HeidiSQL tool was opened in Source PC

4: MySQL server was logged in by typing username and password

5: MySQL Database in which "Orders" table is residing was selected from from Left Panel

6: Database node was expanded and Orders table in the left panel was selected

7: Then on the right panel, "Data" tab was clicked to view the data from "Orders" table as
shown in the picture below

8: A column in the data grid was used for the "Export Grid Rows" option available
9: An Export Grid rows dialog window was viewed. In this window CSV file name as
"test.csv" was entered and "Complete Rows" option was selected. Further “Include Column
Names" checkbox was de selected

10: Ok button was clicked was everything was done

OUTPUT:

RESULT: The database was successfully migrated from MySQL to Oracle database
 EXPERIMENT - 9
AIM: Write a program to Import Utility Usage in Oracle 11G/MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL

THEORY: Objects exported by export utility can only be imported by Import utility. Import
utility can run in Interactive mode or command line mode.

You can let Import prompt you for parameters by entering the IMP command followed by
your username/password:

Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed

by various arguments. To specify parameters, keywords to be used are:

Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)

USERID username/password
BUFFER size of data buffer
FILE input files (EXPDAT.DMP)
SHOW just list file contents (N)
IGNORE ignore create errors (N)
GRANTS import grants (Y)
INDEXES import indexes (Y)
ROWS import data rows (Y)
LOG log file of screen output
FULL import entire file (N)
FROMUSER list of owner usernames
TOUSER list of usernames
TABLES list of table names
RECORDLENGTH length of IO record
INCTYPE incremental import type
COMMIT commit array insert (N)
PARFILE parameter filename
CONSTRAINTS import constraints (Y)
DESTROY overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)
FEEDBACK display progress every x rows(0)
TOID_NOVALIDATE skip validation of specified type ids
FILESIZE maximum size of each dump file
STATISTICS import precomputed statistics (always)
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
COMPILE compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION import streams general metadata (Y)
STREAMS_INSTANITATION import streams instantiation metadata (N)

Example Importing Individual Tables

Individual Tables can be imported from a full database by exporting dump file using the
following command

$imp scott/tiger FILE=myfullexp.dmp FROMUSER=scott TABLES=(emp,dept)

This command will import only emp, dept tables into Scott user and will give an output
similar to as shown below

Export file created by EXPORT:V10.00.00 via conventional path

import done in WE8DEC character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported

Import terminated successfully without warnings.

Example Importing Tables of One User account into another User account

For example, suppose Ali has exported tables into a dump file mytables.dmp. Now Scott
wants to import these tables. To achieve this Scott will give the following import command

$imp scott/tiger FILE=mytables.dmp FROMUSER=ali TOUSER=scott

Then import utility will give a warning that tables in the dump file was exported by user Ali
and not you and then proceed.

Example Importing Tables Using Pattern Matching

Suppose you want to import all tables from a dump file whose name matches a particular
pattern. To do so, use “%” wild character in TABLES option. For example, the following
command will import all tables whose names starts with alphabet “a” and those tables whose
name contains alphabet “d”

$imp scott/tiger FILE=myfullexp.dmp FROMUSER=scott TABLES=(a%,%d%)

Migrating a Database across platforms.

The Export and Import utilities are the only method that Oracle supports for moving an
existing Oracle database from one hardware platform to another. This includes moving
between UNIX and NT systems and also moving between two NT systems running on
different platforms.

The following steps present a general overview of how to move a database between platforms.

As a DBA user, issue the following SQL query to get the exact name of all tablespaces. This
information will be needed later in the process. SQL>
SELECT tablespace_name FROM dba_tablespaces;

As a DBA user, perform a full export from the source database, for example: $ exp
system/manager FULL=y FILE=myfullexp.dmp

Move the dump file to the target database server. If you use FTP, be sure to copy it in binary
format (by entering binary at the FTP prompt) to avoid file corruption.

Create a database on the target server.

Before importing the dump file, you must first create your tablespaces, using the information
obtained in Step 1. Otherwise, the import will create the corresponding datafiles in the same
file structure as at the source database, which may not be compatible with the file structure on
the target system.

As a DBA user, perform a full import with the IGNORE parameter enabled: $
imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp

Using IGNORE=y instructs Oracle to ignore any creation errors during the import and
permit the import to complete.

Perform a full backup of your new database.

RESULT: The usage of Import Utility has been successfully demonstrated in this practical.
 EXPERIMENT-10
AIM: Write a program to use the FLASHBACK Query Feature in Oracle 11G/MySQL
TOOLS REQUIRED: Oracle 10g DBMS, MySQL

THEORY: From Oracle version 9i Oracle has introduced Flashback Query feature.
Accidental failure can be recovered using this feature. Using flash back query accidentally
deleted rows from a table which was even committed.

Undo Retention need to be specified in Flashback feature. By setting UNDO_RETENTION

parameter to 2 hours, Oracle will not overwrite the data in undo tablespace even after
committing until 2 Hours have passed. Users can recover from their mistakes made since last
2 hours only.

Flashback Query
SQL>select * from emp as of timestamp sysdate-1/24;

Or

SQL> SELECT * FROM emp AS OF TIMESTAMP

TO_TIMESTAMP('2007-06-07 10:00:00', 'YYYY-MM-DD HH:MI:SS')

To insert the accidentally deleted rows again in the table he can type

SQL> insert into emp (select * from emp as of timestamp sysdate-1/24)

Using Flashback Version Query

Flashback Version Query is use to retrieve the different versions of specific rows that existed
during a given time interval. Whenever a COMMIT statement is executed a new row version
is created.

A table with a row for each version of the row that existed at any time during the time
interval user specify can be returned using Flashback Version Query. Every table includes
pseudo columns of metadata about the row version., in each row The pseudo columns
available are

VERSIONS_XID :Identifier of the transaction that created the row version

VERSIONS_OPERATION :Operation Performed. I for Insert, U for Update, D for
Delete VERSIONS_STARTSCN :Starting System Change Number when the row version
was created
VERSIONS_STARTTIME :Starting System Change Time when the row version
was created
VERSIONS_ENDSCN :SCN when the row version expired.
VERSIONS_ENDTIME :Timestamp when the row version expired

EXAMPLE

Suppose a user creates an emp table and inserts a row into it and commits the row.
SQL> Create table emp (empno number(5),name varchar2(20),sal number(10,2));
SQL> insert into emp values (101,’Sami’,5000);
SQL>commit;

Currently emp table has one version of one row.

Now a user sitting at another machine erroneously changes the Salary from 5000 to 2000
using Update statement

SQL> update emp set sal=sal-3000 where empno=101;

SQL> commit;

Subsequently, a new transaction updates the name of the employee from Sami to Smith.

SQL>update emp set name=’Smith’ where empno=101;

SQL> commit;

At this point, the DBA detects the application error and needs to diagnose the problem. The
DBA issues the following query to retrieve versions of the rows in the emp table that
correspond to empno 101. The query uses Flashback Version Query pseudo columns

SQL> Connect / as sysdba

SQL> column versions_starttime format a16
SQL> column versions_endtime format a16
SQL> set linesize 120;

SQL> select versions_xid,versions_starttime,versions_endtime,

versions_operation,empno,name,sal from emp versions between
timestamp to_timestamp(‘2007-06-19 20:30:00’,’yyyy-mm-dd hh:mi:ss’)
and to_timestamp(‘2007-06-19 21:00:00’,’yyyy-mm-dd hh:mi:ss’);
VERSION_XID V STARTSCN ENDSCN EMPNO NAME SAL
-
0200100020D U 11323 101 SMITH 2000
02001003C02 U 11345 101 SAMI 2000
0002302C03A I 12320 101 SAMI 5000

The output will show an Insert has taken place and then erroneous update has taken place and
then again update has taken place to change the name.

The DBA identifies the transaction 02001003C02 as erroneous and issues the following
query to get the SQL command to undo the change

SQL> select
operation,logon_user,undo_sql from
flashback_transaction_query
where xid=HEXTORAW(’02001003C02’);
OPERATION LOGON_USER UNDO_SQL

USCOTT update emp set sal=5000 where ROWID =`AAAKD2AABAAAJ29AAA'

Now DBA can execute the command to undo the changes made by the user

SQL> update emp set sal=5000 where ROWID ='AAAKD2AABAAAJ29AAA'

1 row updated

Using Flashback Table to return Table to Past States.

The ability to recover a table or set of tables to a specified point in time in the past very
quickly, easily, and without taking any part of the database offline is provided to DBA by
Oracle Flashback Table. The need to perform more complicated point-in-time recovery
operations is eliminated many times by Flashback Table.

The undo tablespace information is used by Flashback Table to restore the table. Therefore,
UNDO_RETENTION parameter is significant in Flashing Back Tables to a past state.

For issuing the FLASHBACK TABLE statement row movement must be enabled on the
table. We can enable row movement with the following SQL statement:

ALTER TABLE table ENABLE ROW MOVEMENT;

EXAMPLE
FLASHBACK TABLE emp TO TIMESTAMP
TO_TIMESTAMP('2007-06-19 09:30:00', `YYYY-MM-DD HH24:MI:SS');

The emp table is restored to its state when the database was at the time specified by the
timestamp.

EXAMPLE
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the
EMPLOYEE table. This employee was present at 14:00, the last time she ran a report.
Someone accidentally deleted the record for "JOHN" between 14:00 and the present time.
She uses Flashback Table to return the table to its state at 14:00, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP

TO_TIMESTAMP('2007-06-21 14:00:00','YYYY-MM-DD
HH:MI:SS')
ENABLE TRIGGERS;

Recovering Drop Tables (Undo Drop Table)

The concept of Recycle Bin feature was updated in Oracle version 10G i.e., whatever tables
user drop the database does not immediately remove the space used by table. Instead, the
table
is renamed and placed in Recycle Bin. The FLASHBACK TABLE…BEFORE DROP
command will restore the table.

This feature is not dependent on UNDO TABLESPACE so UNDO_RETENTION parameter

has no impact on this feature.

To Recover Drop tables the RECYCLEBIN parameter must be turned ON, otherwise you
will not be able to recover drop tables

For Example, suppose a user accidently drops emp table

SQL>drop table emp;

Table Dropped

Now for user it appears that table is dropped but it is renamed and placed in Recycle Bin. To
recover this dropped table a user can type the command

SQL> Flashback table emp to before drop;

You can also restore the dropped table by giving it a different name like this

SQL> Flashback table emp to before drop rename to emp2;

Purging Objects from Recycle Bin

For the recovery of the space used by a dropped table give the following command

SQL> purge table emp;

To purge objects of login user following command will be given

SQL> purge recycle bin;

To recover space for dropped objects of a particular tablespace following command will be
used

SQL> purge tablespace hr;

Only objects from a tablespace belonging to a specific use can be purged, using the following
form of the command:

SQL> PURGE TABLESPACE hr USER scott;

User can purge all objects from the recycle bin, regardless of which user owns the objects, if
he/she has System privileges by using this command:

SQL> PURGE DBA_RECYCLEBIN;

To view the contents of Recycle Bin give the following command

SQL> show recycle bin;

Permanently Dropping Tables

To permanently drop tables without putting it into Recycle Bin, drop tables with purge
command like this

SQL> drop table emp purge;

This will drop the table permanently and it cannot be restored.

Flashback Drop of Multiple Objects With the Same Original Name

We can create, and then drop, several objects with the same original name, and they will all
be stored in the recycle bin. For example:

CREATE TABLE EMP ( ...columns ); # EMP version

1 DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version
2 DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version
3 DROP TABLE EMP;

Each table EMP is assigned a unique name in the recycle bin when it is dropped. We can use
a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the
table, as shown in this example:

FLASHBACK TABLE EMP TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin,
with its original name. We can retrieve it and assign it a new name using a RENAME TO
clause. The following example shows the retrieval from the recycle bin of all three dropped
EMP tables from the previous example, with each assigned a new name:

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO

EMP_VER_3; FLASHBACK TABLE EMP TO BEFORE DROP RENAME
TO EMP_VER_2; FLASHBACK TABLE EMP TO BEFORE DROP
RENAME TO EMP_VER_1;
OUTPUT:

RESULT: The experiment has been successful in demonstrating FLASHBACK FEATURE

query.