Oracle 9i R2 Database Design, Backup

1
Oracle 9i R2 Database Design, Backup Recovery
on Linux 8

• Masters Project
• RAJASHEKHAR BANDARI

Dept. of Computer Science University of Colorado
at Colorado Springs
2
Outline of the Talk

• Oracle 9i R2 porting on Red Hat Linux 8.
• Database design creation, Networking
• Oracle Backup Recovery tool development using
  bash scripting
• UCCS Admission review Application
• Problems encountered
• Future work

3
Introduction

• My Project work includes the following
  activities
• Installed RedHat Linux 8 with Kernel version 2.4
• Ported Oracle 9i R2 (9.2.0.1.0) on Red Hat Linux
  8 on single CPU PIII NEC
• dual CPU PIII Dell PowerEdge 2550.
• Identified Problems developed fixes for Oracle
  provided scripts.
• Designed Oracle 9i Physical Database and created
  it manually by developing scripts for each
  activity.
• Configured Oracle 9i Networking (Oracle Net)
  using listener.ora, tnsnames.ora, sqlnet.ora
• Developed Oracle 9i Backup Recovery menu
  oriented tool
• - Linux bash scripting
• - Job scheduling
• Developed Framework for UCCS Admission review
  screening Application for PhD, MSCS, MESSE
  Admissions.
• - Logical Database (Normalization), SQL, PL/SQL
  (Triggers, Cursors, Packages)

4
Oracle 9i R2 porting

• System specification
• Dell PowerEdge 2550 PIII dual CPU
• 1 Gig. RAM
• 18 Gig. Hard drive
• Red Hat 8.0 (kernel 2.4.18-18.9.0)
• Oracle 9i Enterprise Edition Release 2
  (9.2.0.1.0)

5
Oracle 9i R2 porting (contd.)

• modify Kernel parameters using the /proc File
  system.
• Create oracle user dba group
• Set the Environment variables and call from
  .bash_profile
• gunzip all the .gz files
• cd /tmp/software
• gunzip lnx_920_disk1.cpio.gz
• gunzip lnx_920_disk2.cpio.gz
• gunzip lnx_920_disk3.cpio.gz
•  
• Apply cpio to all .cpio files
• cpio idmv ltlnx_920_disk1.cpio
• cpio idmv ltlnx_920_disk2.cpio
• cpio idmv ltlnx_920_disk3.cpio

6
Oracle 9i R2 porting (contd.)
Reboot the system inorder to load the new Kernel
parameters! su oracle cd
/tmp/software/Disk1 ./runInstaller
7
Problems encountered

• Error in invoking target install of makefile
  /usr/local/oracle/product/9.2.0/ctx/lib/ins_ctx.mk
  
• Error in invoking target ntcontab.o of makefile
  /opt/oracle/product/9.2.0/network/lib/ins_net
  _client.mk
• libclntsh.so cannot open shared object file No
  such file or directory (libnjni9.so)
• Error loading native library libnjni9.so
• Java.lang.unsatisfiedLinkError
  jniGetOracleHome

8
Oracle 9i Database Architecture
9
Database design creation

• a. Create Physical Database
• startup nomount pfileORACLE_BASE/admin/UCCS/pfil
  e/initUCCS.ora
•  
• create database "UCCS"
• maxlogfiles 48
• maxdatafiles 100
• maxlogmembers 5
• datafile
• '/usr/local/oracle/oradata/system01.dbf' size
  350 m
• logfile group 1 ('/usr/local/oracle/oradata/log
  1a.log',
• /usr/local/oracle/oradata/log1b.log')
  size 50 m reuse,
• group 2 ('/usr/local/oracle/oradata/log2a
  .log',
• '/usr/local/oracle/oradata/log2b.log')
  size 50 m reuse,
• group 3 ('/usr/local/oracle/oradata/
  log3a.log',
• /usr/local/oracle/oradata/log3b.log')
  size 50 m reuse

10
Database design creation (contd.)

• b. Create Data Dictionary
•  
• _at_ORACLE_HOME/rdbms/admin/catalog.sql
• _at_ORACLE_HOME/rdbms/admin/catproc.sql
• disconnect
• - DBA_ views
• - ALL_ views
• - USER_ views

11
Database design creation (contd.)

• c. Create Tablespace Schema
•  REM Create a tablespace for UCCS_ADMIN
  objects
•  create tablespace UCCS_ADMIN
• datafile '/usr/local/oracle/oradata/uccs_admi
  n01.dbf' size 1024 m reuse
• default storage ( initial 512 k next
  512 k pctincrease 0 )
• d. Create UCCS_ADMIN user
•  
• create user UCCS_ADMIN identified by UCCS_ADMIN
• default tablespace UCCS_ADMIN
• temporary tablespace TEMP
• grant connect,resource to UCCS_ADMIN

12
Oracle 9i Database startup/shutdown
13
Oracle 9i Database startup/shutdown
14
Oracle 9i Database STARTUP script problems

• ORACLE_HOME/bin/dbstart doesnt work.
• PFILEORACLE_HOME/dbs/initORACLE_SID.
  ora
• Has to be changed to (fix for oracle scripts)
• if -f ORACLE_HOME/dbs/spfileORACLE_SID
  .ora
• then
• PFILEORACLE_HOME/dbs/spfileORACLE_SID.ora
• else
• PFILEORACLE_HOME/dbs/initORACLE_SID.ora
• fi

15
Oracle 9i Networking

• listener.ora
• tnsnames.ora
• sqlnet.ora

16
Controlling LISTENER(Listener Control Commands)

• Syntax LSNRCTL command
• LSNRCTLgt HELP
• LSNRCTLgt START
• LSNRCTLgt STOP
• LSNRCTLgt RELOAD
• LSNRCTLgt STATUS

17
Troubleshooting Network Problems

• ORA-12154 TNS could not resolve service name
• ORA-12203 TNS unable to connect to destination
• ORA-12224 TNS No Listener

18
Oracle Backup RecoveryRecovery Manager

• Needs recovery catalog to store Repository
• No compression option
• No tape support

19
Oracle 9i Backup Recovery bash scripting
20
Oracle 9i Backup Recovery (contd.)

• Physical Backups
• - cold backups
• - hot backups
• Logical Backups
• exp utility
• Imp utility

21
Oracle 9i Backup Recovery (contd.)

• Algorithm for Hot backups
• SQLgt SELECT tablespace_name,file_name FROM
  dba_data_files
• ALTER TABLESPACE system BEGIN BACKUP
• Execute O/S commands to copy system datafiles
• ALTER TABLESPACE system END BACKUP
• ALTER SYSTEM SWITCH LOGFILE
• Copy all Archive files generated upto 5
• Repeat steps 2 to 6 for all Tablespaces
• ALTER DATABASE BACKUP CONTROLFILE TO
  /usr/local/oracle/backups/

22
Problem identified
23
Hot backups problems fixed

• ORACLE_HOME/bin/dbshut
• doesnt shutdown database, if Database HOT
  backup fails before it completes.
• Solution
• Call my PL/SQL script in dbshut (fix for oracle
  script)
• sqlplus system/manager _at_script_location/ebackup
  .sql

24
Hot backups problems fixed (contd.)

• ALGORITHM
• Queries the DBA_DATA_FILES, VBACKUP and
  identifies the Tablespaces for which Hot backup
  is failed. Creates a cursor with the Results.
• Fetches the Tablespace name from the cursor and
  executes a statement applying END BACKUP using
  DBMS_SQL package.
• Repeats the step 2 for all Tablespaces in the
  cursor.

25
Hot backups problems fixed (contd.)

• DECLARE
• TSNAME VARCHAR2(20)
• cursor_id integer
• ret_val integer
• CURSOR C1 is
• select distinct tablespace_name from
  dba_data_files
• where file_id in (select file from vbackup
  where STATUS'ACTIVE')
• BEGIN
• OPEN C1
• cursor_id dbms_sql.open_cursor
• fetch C1 into TSNAME
• WHILE C1FOUND
• LOOP
• dbms_sql.parse(cursor_id,'alter tablespace
  'TSNAME' end backup',dbms_sql.NATIVE)
• ret_val dbms_sql.execute(cursor_id)
• FETCH C1 INTO TSNAME
• END LOOP
• dbms_sql.close_cursor(cursor_id)
• CLOSE C1

26
Backup Recovery - Job scheduling

• crontab entries from Dell PowerEdge 2550 for
  oracle user
• 04 08 /usr/local/oracle/scripts/nightly_orac
  le_exports.ksh 3 2gt/dev/null
• 08 16 /usr/local/oracle/scripts/coldbackup.s
  ql 3 2gt/dev/null
• 45 23 sqlplus system/manager
  _at_/usr/local/oracle/scripts/hotbackup.sql
• 18 /usr/local/oracle/scripts/arch_compres
  s.sh 2gt/dev/null

27
UCCS Admission review Application
28
UCCS Admission review Application (contd.)

• Logical Database(Normalized) for PHD Review Form
  
• http//cs.uccs.edu/gsc/PhDReviewForm.htm
• REM SQL DDL statements for creating various
  tables requied by PHD Review forms
• DROP TABLE PHD_REVIEW
• CREATE TABLE PHD_REVIEW
• (SSN VARCHAR2(11) CONSTRAINT PHD_REVIEW_PK
  PRIMARY KEY,
• APPL_LNAME VARCHAR2(20),
• APPL_FIRST_MIDDLE VARCHAR2(30),
• PROGRAM_APPLIED VARCHAR2(5),
• CITIZENSHIP VARCHAR2(40),
• PR_STATUS CHAR(1) CHECK (PR_STATUS IN
  ('Y','N')),
• TOEFL NUMBER(5,2),
• UG_GPA NUMBER(2,1),
• GRE_VERBAL NUMBER(2),
• GRE_ANALYTICAL NUMBER(2),
• GRE_QUANTATIVE NUMBER(2),
• COMMITTEE_LOGIN1 VARCHAR2(10),

29
UCCS Admission review Application (contd.)

• PHD Review Form
• http//cs.uccs.edu/gsc/PhDReviewForm.htm
• REM SQL DDL statements for creating various
  tables requied by PHD Review forms
•  
• DROP TABLE PHD_COMM_EVALUATION
• CREATE TABLE PHD_COMM_EVALUATION
• (PROF_LOGIN VARCHAR2(20),
• PASSWORD VARCHAR2(10),
• APPLICANT_SSN VARCHAR2(11 ),
• REG_DEGREE CHAR(1) CHECK (REG_DEGREE IN
  ('Y','N')),
• TWO_SEM_CALC CHAR(1) CHECK (TWO_SEM_CALC IN
  ('Y','N')),
• DISC_MATHS CHAR(1) CHECK (DISC_MATHS IN
  ('Y','N')),
• PROBABILITY_STATS CHAR(1) CHECK
  (PROBABILITY_STATS IN ('Y','N')),
• LIN_ALZEBRA CHAR(1) CHECK (LIN_ALZEBRA IN
  ('Y','N')),
• ALG_GRA_DIFF CHAR(1) CHECK (ALG_GRA_DIFF IN
  ('Y','N')),
• SUGGESTION VARCHAR2(2) CHECK (SUGGESTION IN
  ('A','R','PA')),
• PROF_COMMENT VARCHAR2(2000))

30
Database Trigger decides Admission Status

• REM Script to create Triggers that fires when
  UCCS_ADMIN tables are accessed
• CREATE OR REPLACE TRIGGER PHD_REVIEW_TRIG
• AFTER INSERT ON PHD_COMM_ EVALUATION
• DECLARE
• CURSOR APPL_COMM_CUR IS
• SELECT APPL_SSN FROM PHD_REVIEW WHERE
  ADM_STATUSNULL OR ADM_STATUS'UNDER PROCESS'
• TCNT NUMBER(2)
• CNT NUMBER(2)
• BEGIN
• OPEN APPL_COMM_CUR
• FOR I IN APPL_COMM_CUR
• LOOP
• SELECT COUNT() INTO TCNT FROM
  PHD_COMM_EVALUATION WHERE APPL_SSNI.APPL_SSN
• IF TCNT3 THEN
• SELECT COUNT() INTO CNT FROM PHD_COMM_
  EVALUATION WHERE APPL_SSNI.APPL_SSN
• AND SUGGESTION IN
  ('ACCEPTED','PROVISIONALLY ACCEPTED')
• IF CNT3 THEN
• INSERT INTO PHD_REVIEW (ADM_STATUS)
  VALUES ('ACCEPTED') WHERE APPL_SSNI.APPL_SSN
• ELSIF

31
Future work for web module

• Oracle HTTP or Apache with Tomcat has to be
  installed and configured for accessing Oracle 9i
  Database thru JSPs.

32
Conclusion

• Red Hat Linux 8 is Installed on Dell PowerEdge
  2550
• Oracle 9.2.0 successfully ported on Red Hat Linux
  8 (kernel ), identified the problems developed
  some fixes for Oracle provided scripts.
• Developed SQL scripts and manually created 9i
  Database.
• Designed Normalized Logical Database for UCCS
  Admission review Application.
• Developed menu driven Backup Recovery tool
  using bash scripts and scheduled scripts to run
  thru crontab.

33
Future work

• Automated Backup Recovery tool can be improved
  with additional options, So that anybody can use
  it.
• Database can be optimized with more proper I/O
  distribution provided multiple disks available.

34

• Questions?

35
References

• 1.   Oracle 9i The Complete Reference (Oracle
  Press/Osborne)
• 2.   Oracle 9i Release 1 (9.0.1) for Linux Intel,
  Part No. A90352-02 from Oracle Customer Support
  
• web site metalink.oracle.com.
• 3.   Note 176865 LINUX Quick Start Guide
  9.0.1 RDBMS Installation from
• metalink.oracle.com.
• 4.   Introduction to Oracle 9i SQL, PL/SQL, and
  SQLPlus from elementk by George Callaway.
• 5.    Oracle 9i OCP Exam Fundamentals I - Jason
  Couchman Sudhir Marisetti from Oracle
  
• Press/Osborne.
• 6.    Learning bash Shell, 2nd Edition -
  Cameron Newham Bill Rosenblatt from OReilly
  
• publishers.
• 7.    OCP Oracle 9i Database Fundamentals II
  - Rama Velpuri from Oracle Press/Osborne.
• 8.    Oracle Backp Recovery Hand Book
  - Rama Velpuri from Oracle Press/Osborne.
• 9.    More servlets and Java server pages by
  Marty Hall, Upper Saddle River, NJ
• Prentice Hall, c2002.
• 10.  Core servlets and JavaServer Pages by Marty
  Hall, Upper Saddle River, NJ
• Prentice Hall PTR, c2000
• 11. Thinking in Java by Bruce Eckel Upper Saddle
  River, N.J. Prentice Hall, c1998.
• 12.  Database programming with JDBC and Java by
  George Reese.
• 13. Discovering HTML 4 by Bryan Pfaffenberger.

36

• Thanks!
• to
• my Advisor committee Professors for their
  constant support encouragement!!!

37
UCCS Admission review ApplicationPhase-1
38
UCCS Admission review ApplicationPhase-2
39
UCCS Admission review ApplicationPhase-3