Figure 2 Allocation of size for Linux
Figure 1 Linux installation The location and size have been allocated for the
The name of the operating system has been given operating system. Size has been allocated
‘oralinux’ and type of the system has been chosen dynamically 120 GB
as ‘Linux’ in the above given figure
Figure 3 loading of Linux ISO in the Virtual box Figure 4 Starting of Linux initialization
The Linux ISO file has been loaded in the Virtual The installation process of Linux has been started
box for booting
Figure 1 Selection of Language Figure 2 Basic storage device chosen
English language has been selected for the Basic storage device has been chosen. Specialized
operating system storage devices are chosen by specialized personnel
1
1
Figure 3 password assign
Password has been assigned for the system for Figure 4 selection of time zone
authentication purpose Oracle doesn’t support time zone in Nepal, so I have
chosen time zone of New York city, United States of
America
Custom layout has been selected for new installation.
Figure 5 selection of types of installation
The following partition has been created for Linux
installation:
/boot: required for starting of operating system
/home: storing files of desktop
/tmp: execution of third parties’ software is
done here
/u01: this partition is suggested by oracle
/swap: works as temporary RAM, known as
paging.
Figure 6 creation of partition and disk
Different required icons needed for the Desktop are
selected for the Linux.
Figure 7 selection of desktop icons
2
Figure 8 finalizing of oracle installation Figure 9 license agreement
The installation of Linux 6.7 has been successful License agreement has been agreed
Figure 10 creation of user and password Figure 11 enabling Kdump
User has been created and password has been Kdump is a kernel crash dumping mechanism and
given captures information that helps in finding the cause
of crash
Figure 12 Configuring IP address Figure 13 Putty configured
The IP has been configured to connect the server The Putty has been configured and connected with
server entering the IP address
3
Required files for grid users are inserted in the Yum has been successfully installed in the system
root user for initialization through USB so that all the required softwares are installed
serially
All the necessary groups like dba, oper, admin, All the necessary variables environment needed
asmdba has been created for grid and oracle for grid user are initialized
users
All the necessary variables environment for 6 disks are created and each partition of 5 GB has
oracle user are initialized been created for the project
ASM:
4
5
All the necessary packages and files required for The disk that we have parted are marked in the
automatic storage management has been installed above step so that OS can understand it
All the infrastructure required grid user for asm The required infrastructure has been selected and
are being installed configured
English language has been selected 3 disks have been selected for asm i.e. VOL1,2,3
Asm configuration assistance for data storage 3 disks have been selected for Fast Recovery Area
(FRA)
6
Figure 14 Disk group for asm Figure 15 installing database
2 groups of each 3 partition has been created for Oracle database for 11g has been done
asm
Enterprise edition for database has been selected Database has been created with name ‘Healthcare’
and installed
Recovery area for my database has been selected with
enabling archiving
Why Linux:
There are many reasons why Linux should be preferred over proprietary software platform such as
Windows and Mac. But here, we are listing the top ten reasons to give you the clear picture.
7
Until a few years ago, Linux was used mainly for servers and was not considered suitable for desktops.
But its user interface and ease of use has been steadily improving over the last few years. Linux has
today become user-friendly enough to replace Windows on desktops. It is being used by hundreds of
thousands of people across the globe. Here are the ten most important reasons why. Windows license
cost is different for both desktop and server versions. In case of Linux OS either it can be desktop or
server, distro comes with no cost. Windows OS is one of the simplest desktop OS available today. Its
graphical user-friendliness is exceptional. Linux is more reliable when compared to Windows. Linux will
rock with its top-notch design, built-in security resulting un-parallel up-time. Linux systems are known for
consuming fewer system resources (RAM, disk space etc. when compared to Windows. No doubt that
Windows has a large set of commercial software available. Linux, on the other hand, makes use of open
source software available for free. Microsoft Windows OS is infamous for being vulnerable to malware,
Trojans, and viruses. Linux is almost non-vulnerable and more secure due to its inherent design. Linux
can be installed and used it as a desktop, firewall, a file server, or a web server. Linux allows a user to
control every aspect of the operating systems. As Linux is an open-source operating system, it allows a
user to modify its source even source code of applications itself as per the user requirements. There are
times when Windows suddenly shows an annoying message saying that the machine needs to be
restarted. Linux is installed on the majority of servers demonstrating that it is the best choice with the
minimal resource footprint. Even rivals are using Linux on their offerings. From smallest device to largest
supercomputers, Linux is everywhere. It can be a car, router, phone, medical devices, plane, TV,
satellite, watch or school tablet, Linux will be there.
As software applications are moving to cloud platforms, windows servers are getting phased out to make
room for Linux servers. Majority of the supercomputers to run on Linux. There is a saying – variety is the
spice of life. It is true with respect to Linux distros. There are more than 600 active different distros to
choose. Each is different on its own and meant for the specific purpose. Linux distros are highly
customizable when compared to Windows.
8
Tablespace
CREATE UNDO TABLESPACE UNDO_BANK
DATAFILE '+DATA' SIZE 1000M AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED
RETENTION GUARANTEE;
ALTER system SET undo_tablespace=UNDO_BANK scope=both undo_retention=5;
CREATE TEMPORARY TABLESPACE temp_bank
TEMPFILE '+DATA(DATAFILE)/BANK_TEMP_DATAFILE' SIZE 100M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_bank;
Creating User
CREATE USER ashish IDENTIFIED BY ashish
DEFAULT TABLESPACE bank_tbs
TEMPORARY TABLESPACE bank_temp_tbs
QUOTA UNLIMITED ON bank_tbs;
Password Verification
CREATE OR REPLACE FUNCTION bank_verification
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean; --for password complexity
ischar boolean; --for password complexity
ispunct boolean; --for password complexity
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
BEGIN
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
9
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer',
'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
RETURN(TRUE);
END;
Creating profile
CREATE PROFILE bank_profile LIMIT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME 60
SESSIONS_PER_USER 10
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
PRIVATE_SGA DEFAULT
COMPOSITE_LIMIT DEFAULT
FAILED_LOGIN_ATTEMPTS 3 -- Account locked after 3 failed logins.
PASSWORD_LOCK_TIME 5 -- Number of days account is locked for. UNLIMITED required explicit
unlock by DBA.
PASSWORD_LIFE_TIME 30 -- Password expires after 90 days.
PASSWORD_GRACE_TIME 3 -- Grace period for password expiration.
PASSWORD_REUSE_TIME 120 -- Number of days until a specific password can be reused.
UNLIMITED means never.
PASSWORD_REUSE_MAX 10 -- The number of changes required before a password can be reused.
UNLIMITED means never.
PASSWORD_VERIFY_FUNCTION bank_verification;
Creating user for the profile
CREATE USER bank
PROFILE bank_profile
IDENTIFIED BY password123 PASSWORD EXPIRE
DEFAULT TABLESPACE bank_tbs
TEMPORARY TABLESPACE temp_bank ACCOUNT UNLOCK;
GRANT CONNECT,RESOURCE,DBA TO bank;
10
Performing ETL
Making control file
Making staging table
Loading data into staging table
Making main table
Inserting into main table
LOAD DATA
INFILE '/u01/app/external/ transaction_type.txt'
APPEND INTO TABLE SYSTEMADMIN.ext_transaction_type
FIELDS TERMINATED BY '|'
(
transaction_type_id char(200),
transaction_type char(200)
)
-------making control file
CREATE TABLE ext_transaction_type(
transaction_type_id VARCHAR2(255),
transaction_type VARCHAR2(255)
);
--DROP TABLE ext_transaction_type;
--------making staging table
sqlldr userid=systemadmin/systemadmin12345# control=/u01/app/ext/ transaction_type.ctl
log=/u01/app/ext/ transaction_typelog.log bad=/u01/app/ext/ transaction_type.bad;
------loading data into staging table
CREATE TABLE transaction_type(
transaction_type_id VARCHAR2(255) not null,
transaction_type VARCHAR2(255) not null
);
ALTER TABLE transaction_type ADD CONSTRAINTS transaction_type_id_pk PRIMARY KEY
(transaction_type_id);
------making main table
INSERT INTO transaction_type
SELECT
transaction_type_id, transaction_type
FROM ext_transaction_type;
----inserting into main table
11
LOAD DATA
INFILE '/u01/app/external/ customer.txt'
APPEND INTO TABLE SYSTEMADMIN.ext_customer
FIELDS TERMINATED BY '|'
(
cust_id char(255),
cust_first_name char(255),
cust_last_name char(255),
cust_age char(255),
marital_status char (255),
job char (255),
gender char (255),
education char (255),
income char (255),
phonenumber char (255),
country char (255)
)
CREATE TABLE ext_customer(
cust_id VARCHAR2(255),
cust_first_name VARCHAR2(255),
cust_last_name VARCHAR2(255),
cust_age VARCHAR2(255),
marital_status VARCHAR2(255),
job VARCHAR2(255),
gender VARCHAR2(255),
education VARCHAR2(255),
income VARCHAR2(255),
phonenumber VARCHAR2(255),
country VARCHAR2(255)
);
--DROP TABLE ext_customer;
sqlldr userid=systemadmin/systemadmin12345# control=/u01/app/ext/ customer.ctl log=/u01/app/ext/
customerlog.log bad=/u01/app/ext/ customer.bad;
CREATE TABLE customer (
cust_id VARCHAR2(255)not null,
cust_first_name VARCHAR2(255) not null,
cust_last_name VARCHAR2(255) not null,
cust_age VARCHAR2(255) not null,
marital_status VARCHAR2(255) not null,
job VARCHAR2(255) not null,
gender VARCHAR2(255) not null,
education VARCHAR2(255) not null,
income VARCHAR2(255) not null,
phonenumber VARCHAR2(255) not null,
country VARCHAR2(255) not null
);
ALTER TABLE customer ADD CONSTRAINTS cust_id_pk PRIMARY KEY (cust_id);
INSERT INTO customer
SELECT
cust_id, cust_first_name, cust_last_name, cust_age, marital_status, job, gender, education, income,
phonenumber, country
FROM ext_customer;
12
LOAD DATA
INFILE '/u01/app/external/ account.txt'
APPEND INTO TABLE SYSTEMADMIN.ext_account
FIELDS TERMINATED BY '|'
(
account_id char(200),
balance char(200)
)
CREATE TABLE ext_account(
account_id VARCHAR2(255),
balance VARCHAR2(255)
);
--DROP TABLE ext_account
sqlldr userid=systemadmin/systemadmin12345# control=/u01/app/ext/ account.ctl log=/u01/app/ext/
accountlog.log bad=/u01/app/ext/ account.bad;
CREATE TABLE account(
account_id VARCHAR2(255)not null,
balance VARCHAR2(255) not null
);
ALTER TABLE account ADD CONSTRAINTS account_id_pk PRIMARY KEY (account_id);
INSERT INTO account
SELECT
account_id, balance
FROM ext_account;
LOAD DATA
INFILE '/u01/app/external/ loan_payment.txt'
APPEND INTO TABLE SYSTEMADMIN.ext_loan_payment
FIELDS TERMINATED BY '|'
(
loan_payment_id char(200),
payment_date char(200),
payment_amount char(200)
)
CREATE TABLE ext_loan_payment (
loan_payment_id VARCHAR2(255),
payment_date VARCHAR2(255),
payment_amount VARCHAR2(255)
);
--DROP TABLE ext_loan_payment;
sqlldr userid=systemadmin/systemadmin12345# control=/u01/app/ext/ loan_payment.ctl
log=/u01/app/ext/ loan_paymentlog.log bad=/u01/app/ext/ loan_payment.bad;
CREATE TABLE loan_payment(
loan_payment_id VARCHAR2(255) not null,
payment_date VARCHAR2(255) not null,
payment_amount VARCHAR2(255) not null
);
13
ALTER TABLE loan_payment ADD CONSTRAINTS loan_payment_id_pk PRIMARY KEY
(loan_payment_id);
INSERT INTO loan_payment
SELECT
loan_payment_id, payment_date, payment_amount
FROM ext_loan_payment;
LOAD DATA
INFILE '/u01/app/external/ loan.txt'
APPEND INTO TABLE SYSTEMADMIN.ext_loan
FIELDS TERMINATED BY '|'
(
loan_id char(255),
loan_issued_date char(255),
loan_type char(255),
loan_amount char(255)
)
CREATE TABLE ext_loan (
loan_id VARCHAR2(255),
loan_issued_date VARCHAR2(255),
loan_type VARCHAR2(255),
loan_amount VARCHAR2(255)
);
--DROP TABLE ext_loan;
sqlldr userid=systemadmin/systemadmin12345# control=/u01/app/ext/ loan.ctl log=/u01/app/ext/
loanlog.log bad=/u01/app/ext/ loan.bad;
CREATE TABLE loan (
loan_id VARCHAR2(255) not null,
loan_issued_date VARCHAR2(255) not null,
loan_type VARCHAR2(255) not null,
loan_amount VARCHAR2(255) not null
);
ALTER TABLE loan ADD CONSTRAINTS loan_id_pk PRIMARY KEY (loan_id);
INSERT INTO loan
SELECT
loan_id, loan_issued_date, loan_type, loan_amount
FROM ext_loan;
14
LOAD DATA
INFILE '/u01/app/external/ transaction.txt'
APPEND INTO TABLE SYSTEMADMIN.ext_transaction
FIELDS TERMINATED BY '|'
(
transaction_id char(255),
cust_id char(255),
account_id char(255),
transaction_type_id char(255),
loan_id char(255),
loan_payment_id char(255)
)
CREATE TABLE transaction (
transaction_id VARCHAR2(255),
cust_id VARCHAR2(255),
account_id VARCHAR2(255),
transaction_type_id VARCHAR2(255),
loan_id VARCHAR2(255),
loan_payment_id VARCHAR2(255));
--DROP TABLE ext_loan;
sqlldr userid=systemadmin/systemadmin12345# control=/u01/app/ext/ transaction.ctl log=/u01/app/ext/
transactionlog.log bad=/u01/app/ext/ transaction.bad;
CREATE TABLE transaction (
transaction_id VARCHAR2(255) not null,
cust_id VARCHAR2(255) not null,
account_id VARCHAR2(255) not null,
transaction_type_id VARCHAR2(255) not null,
loan_id VARCHAR2(255) not null,
loan_payment_id VARCHAR2(255) not null
);
ALTER TABLE transaction ADD CONSTRAINTS transaction_id_pk PRIMARY KEY (transaction_id);
INSERT INTO transaction
SELECT
loan_id, cust_id, account_id, transaction_type_id, loan_id, loan_payment_id
FROM ext_loan;
15
LOAD DATA
INFILE '/u01/app/external/ employee.txt'
APPEND INTO TABLE SYSTEMADMIN.ext_employee
FIELDS TERMINATED BY '|'
(
emp_id char(255),
emp_first_name char(255),
emp_last_name char(255),
department char(255),
branch char(255),
address char(255),
salary char(255)
)
CREATE TABLE ext_employee (
emp_id VARCHAR2(255),
emp_first_name VARCHAR2(255),
emp_last_name VARCHAR2(255),
department VARCHAR2(255),
branch VARCHAR2(255),
address VARCHAR2(255),
salary VARCHAR2(255)
);
--DROP TABLE ext_employee;
sqlldr userid=systemadmin/systemadmin12345# control=/u01/app/ext/ employee.ctl log=/u01/app/ext/
employeelog.log bad=/u01/app/ext/ employee.bad;
CREATE TABLE employee(
emp_id VARCHAR2(255) NOT NULL,
emp_first_name VARCHAR2(255) NOT NULL,
emp_last_name VARCHAR2(255) NOT NULL,
department VARCHAR2(255) NOT NULL,
branch VARCHAR2(255) NOT NULL,
address VARCHAR2(255) NOT NULL,
salary VARCHAR2(255) NOT NULL);
ALTER TABLE employee ADD CONSTRAINTS emp_id_pk PRIMARY KEY (emp_id);
INSERT INTO employee
SELECT
emp_id, emp_first_name, emp_last_name, department, branch, address, salary
FROM ext_employees;
16
RMAN
Catdb database has been created as sys user and following command is executed.
CREATE TABLESPACE tbs_rman
DATAFILE '+BANK/rman.dbf' SIZE 6208K REUSE
AUTOEXTEND ON NEXT 64K MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE tbs_rman
QUOTA UNLIMITED ON tbs_rman;
GRANT connect, resource, recovery_catalog_owner TO rman;
Oracle is connected in OS level and following command is executed.
The recovery catalog is connected as the catalog owner
$ rman catalog rman/rman@catdb
All the information related to catalog is stored in “tbs_rman” tablespace
RMAN> CREATE CATALOG TABLESPACE tbs_rman;
RMAN> EXIT
After verifying register and listing incarnation
CREATE USER bankdb IDENTIFIED BY bankdb;
GRANT connect, resource, recovery_catalog_owner TO bank;
New Database bank is created and registered in catdb
$ rman target sys/password@newdb catalog rman/rman@catdb
RMAN> grant catalog for database bank TO bankdb;
RMAN> host;
After that a virtual catalog is created
RMAN> CREATE virtual catalog;
RMAN> list incarnation;
RMAN> EXIT;
17
Flashback
CREATE TABLESPACE flashback1 datafile SIZE 100M;
CREATE undo tablespace undoflash1 datafile SIZE 100M;
CREATE TEMPORARY TABLESPACE temp2;
CREATE USER fback1 IDENTIFIED BY fback
DEFAULT TABLESPACE flashback1
TEMPORARY TABLESPACE temp2
--undo TABLESPACE undoflash
QUOTA UNLIMITED ON flashback1;
ALTER system SET undo_tablespace=undoflash1 scope=both;
GRANT CONNECT,RESOURCE,DBA TO fback1;
CREATE TABLE flashback_query_test (
id NUMBER(10)
);
SELECT current_scn,
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
FROM v$database;
-- 2179632 2019-12-10 01:09:06
INSERT INTO flashback_query_test (id) VALUES (1);
--2179649 2019-12-10 01:09:21
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
INSERT INTO flashback_query_test (id) VALUES (2);
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
--2179691 2019-12-10 01:09:44
INSERT INTO flashback_query_test (id) VALUES (3);
--2179701 2019-12-10 01:10:08
ALTER TABLE flashback_query_test
ENABLE ROW movement;
SELECT *
FROM flashback_query_test AS
OF TIMESTAMP TO_TIMESTAMP('2019-12-10 01:09:44',
'YYYY-MM-DD HH24:MI:SS');
SELECT * FROM flashback_query_test;
flashback TABLE flashback_query_test TO
TIMESTAMP to_timestamp('2019-12-10 01:09:44', 'YYYY-MM-DD HH24:MI:SS');
SELECT * FROM flashback_query_test;
flashback TABLE flashback_query_test TO SCN 1322918;
18
FLASHBACK VERSIONS QUERY
CREATE TABLE flashback_version_query_test (
id NUMBER(10),
description VARCHAR2(50)
);
INSERT INTO flashback_version_query_test
(id, description) VALUES (1, 'ONE');
COMMIT;
SELECT current_scn,
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
--2181058 2019-12-10 01:18:39
UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
COMMIT;
UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
COMMIT;
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
--2181127 2019-12-10 01:20:09
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query_test
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2019-12-10 01:18:20', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2019-12-10 01:25:09', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1;
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
ALTER TABLE flashback_version_query_test ENABLE ROW movement;
flashback TABLE flashback_version_query_test TO SCN 2181445;
SELECT * FROM flashback_version_query_test;
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query ;
WHERE logon_user='FBACK1';
19
20
