Hello! How you doing? Today I want to share about what I’ve learned on
my 3 months training with GBS Iskandar about Oracle Database. On my first week,
I’ve learned about Oracle Database Architecture which are storage structure,
tablespace, data files, and Automatic Storage Management (ASM). We need to
fully understand on the architecture before move further into the details of
studies of Oracle Database. Before entering the data, a table need to be
created. A tablespace is needed in Oracle Database to store data inserted into
the database.
We also learned about Oracle
Database Management Tools and the basic three are SQL Plus, SQL Developer, and
Enterprise Manager. SQL Developer is a tool for beginners start querying some
data but usually being used for unit testing. SQL Developer need to be
configured well before use and the database has to be created. While Enterprise
Manager is a web based Oracle Database application to configure the privileges,
security, and data files. It can be logged in as sysdba or normal user. While SQL
Plus is a more like command tool and the functionalities are not as much as SQL
Developer. SQL Plus can be called in terminal in Oracle Virtual Machine
Terminal but need to start the listener first.
Next, managing Database Instance
using Initialization parameters, Listener and Sessions. These are the steps
needed before connecting to the database. The listener need to be configured
well because it is the crucial part. If the listener is wrong, the database
will refuse to connect because it didn’t found the listener. We also been given
chance to do some training on setting up database instance because every
morning we need to start the database before start the class.
We also learned on administering
user security such as privileges, authenticating, user roles, and password
profiles. The user security administration can be accessed only by user with
sysdba privileges, in Enterprise Manager. Oracle database also provide Block
Space Management, Row Chaining and Migration, Compression, and Managing Undo
Data which divided into two parts; Managing Undo and Temporary Undo. On managing
undo data, we were using the undo advisor.
At the end of the first week, we
learned on managing data concurrency such as locks, DML lock, lock conflicts, and
deadlocks. When the deadlocks occur, Oracle Database automatically detects and
terminates the statement with and error. The proper response to the error is
either commit or rollback, which will release any other locks in that session
so that the other session can continue its transaction.
On week two, we learned on
backup and recovery which have been divided into some parts; categories of
failures, flashback, checkpoint, redo lock, and point-in-time recovery. There
are some categories of failure;
statement failure (a single database operation; select, insert,
update, or delete fails),
user process failure (a single database session fails), network
failure (connectivity to the database is lost),
user error (a user successfully completes an operation, but the operation is
incorrect),
instance failure (the database instance shuts down unexpectedly),
media failure (a loss of any file that is needed for database operation).
Oracle provides an
appropriate data protection and solution depending on user’s backup and
recovery objective; Oracle Recovery Manager (RMAN), Oracle Secure Backup (OSB),
Oracle Database Flashback, Data Recovery Advisor, and Data Guard and Active
Data Guard.
Next, we also learned on defining
the data using select statement from basic to advance, restricting and sorting
data to limit and sort the rows that are retrieved by a query and learn to use
ampersand substitution. We need to use WHERE clause, comparison conditions, and
logical conditions such as AND, OR, and NOT operators. Next, how to customize
the output. Because of the variation of data type, there are types of
customizing the output; single-row SQL functions containing character, number,
and date, conversion and general functions.
No comments:
Post a Comment