Oracle 9 i R 2 Database Design Backup

Oracle 9 i R 2 Database Design, Backup & Recovery on Linux 8 Master’s Project RAJASHEKHAR BANDARI Dept. of Computer Science University of Colorado at Colorado Springs 3/6/2021 Rajashekhar Bandari 1

Outline of the Talk n n n 3/6/2021 Oracle 9 i R 2 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 Rajashekhar Bandari 2

Introduction My Project work includes the following activities: n n n Installed Red. Hat Linux 8 with Kernel version: 2. 4 Ported Oracle 9 i R 2 (9. 2. 0. 1. 0) on Red Hat Linux 8 on single CPU PIII NEC & dual CPU PIII Dell Power. Edge 2550. Identified Problems & developed fixes for Oracle provided scripts. Designed Oracle 9 i Physical Database and created it manually by developing scripts for each activity. Configured Oracle 9 i Networking (Oracle Net) using listener. ora, tnsnames. ora, sqlnet. ora Developed Oracle 9 i Backup & Recovery menu oriented tool - Linux bash scripting - Job scheduling Developed Framework for UCCS Admission review screening Application for Ph. D, MSCS, MESSE Admissions. - Logical Database (Normalization), SQL, PL/SQL (Triggers, Cursors, Packages) 3/6/2021 Rajashekhar Bandari 3

Oracle 9 i R 2 porting System specification: n n n 3/6/2021 Dell Power. Edge 2550 PIII dual CPU 1 Gig. RAM 18 Gig. Hard drive Red Hat 8. 0 (kernel 2. 4. 18 -18. 9. 0) Oracle 9 i Enterprise Edition Release 2 (9. 2. 0. 1. 0) Rajashekhar Bandari 4

Oracle 9 i R 2 porting (contd. ) n n n 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_disk 1. cpio. gz $ gunzip lnx_920_disk 2. cpio. gz $ gunzip lnx_920_disk 3. cpio. gz Apply “cpio” to all *. cpio files $ cpio –idmv <lnx_920_disk 1. cpio $ cpio –idmv <lnx_920_disk 2. cpio $ cpio –idmv <lnx_920_disk 3. cpio 3/6/2021 Rajashekhar Bandari 5

Oracle 9 i R 2 porting (contd. ) Reboot the system inorder to load the new Kernel parameters…! # su – oracle $ cd /tmp/software/Disk 1 $. /run. Installer 3/6/2021 Rajashekhar Bandari 6

Problems encountered n n n 3/6/2021 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 (libnjni 9. so) Error loading native library : libnjni 9. so Java. lang. unsatisfied. Link. Error : jni. Get. Oracle. Home Rajashekhar Bandari 7

Oracle 9 i Database Architecture 3/6/2021 Rajashekhar Bandari 8

Database design & creation a. Create Physical Database startup nomount pfile=$ORACLE_BASE/admin/UCCS/pfile/init. UCCS. ora create database "UCCS" maxlogfiles 48 maxdatafiles 100 maxlogmembers 5 datafile '/usr/local/oracle/oradata/system 01. dbf' size 350 m logfile group 1 ('/usr/local/oracle/oradata/log 1 a. log', ‘/usr/local/oracle/oradata/log 1 b. log') size 50 m reuse, group 2 ('/usr/local/oracle/oradata/log 2 a. log', '/usr/local/oracle/oradata/log 2 b. log') size 50 m reuse, group 3 ('/usr/local/oracle/oradata/log 3 a. log', ‘/usr/local/oracle/oradata/log 3 b. log') size 50 m 3/6/2021 Rajashekhar Bandari reuse; 9

Database design & creation (contd. ) b. Create Data Dictionary @$ORACLE_HOME/rdbms/admin/catalog. sql @$ORACLE_HOME/rdbms/admin/catproc. sql disconnect; - DBA_% views - ALL_% views - USER_% views 3/6/2021 Rajashekhar Bandari 10

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_admin 01. 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; 3/6/2021 Rajashekhar Bandari 11

Oracle 9 i Database – startup/shutdown 3/6/2021 Rajashekhar Bandari 12

Oracle 9 i Database – startup/shutdown 3/6/2021 Rajashekhar Bandari 13

Oracle 9 i Database – STARTUP script problems n $ORACLE_HOME/bin/dbstart doesn’t work. PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}. ora Has to be changed to : (fix for oracle scripts) if [ -f ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}. ora ] then PFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}. ora else PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}. ora fi 3/6/2021 Rajashekhar Bandari 14

Oracle 9 i Networking n n n 3/6/2021 listener. ora tnsnames. ora sqlnet. ora Rajashekhar Bandari 15

Controlling LISTENER (Listener Control Commands) Syntax : LSNRCTL command LSNRCTL> HELP LSNRCTL> START LSNRCTL> STOP LSNRCTL> RELOAD LSNRCTL> STATUS 3/6/2021 Rajashekhar Bandari 16

Troubleshooting Network Problems ORA-12154: TNS: could not resolve service name ORA-12203: TNS: unable to connect to destination ORA-12224: TNS: No Listener 3/6/2021 Rajashekhar Bandari 17

Oracle Backup & Recovery Manager - 3/6/2021 Needs recovery catalog to store Repository No compression option No tape support Rajashekhar Bandari 18

Oracle 9 i Backup & Recovery – bash scripting 3/6/2021 Rajashekhar Bandari 19

Oracle 9 i Backup & Recovery (contd. ) n n Physical Backups - cold backups - hot backups Logical Backups - 3/6/2021 exp utility Imp utility Rajashekhar Bandari 20

Oracle 9 i Backup & Recovery (contd. ) Algorithm for Hot backups: 1. 2. 3. 4. 5. 6. 7. 8. 3/6/2021 SQL> 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/…’; Rajashekhar Bandari 21

Problem identified 3/6/2021 Rajashekhar Bandari 22

Hot backups & problems fixed $ORACLE_HOME/bin/dbshut doesn’t 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 @${script_location}/ebackup. sql 3/6/2021 Rajashekhar Bandari 23

Hot backups & problems fixed (contd. ) ALGORITHM: 1. 2. 3/6/2021 Queries the DBA_DATA_FILES, V$BACKUP 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. Rajashekhar Bandari 24

Hot backups & problems fixed (contd. ) DECLARE TSNAME VARCHAR 2(20); cursor_id integer; ret_val integer; CURSOR C 1 is select distinct tablespace_name from dba_data_files where file_id in (select file# from v$backup where STATUS='ACTIVE'); BEGIN OPEN C 1; cursor_id : = dbms_sql. open_cursor; fetch C 1 into TSNAME; WHILE C 1%FOUND LOOP dbms_sql. parse(cursor_id, 'alter tablespace '||TSNAME||' end backup', dbms_sql. NATIVE); ret_val : = dbms_sql. execute(cursor_id); FETCH C 1 INTO TSNAME; END LOOP; dbms_sql. close_cursor(cursor_id); CLOSE C 1; END; / 3/6/2021 Rajashekhar Bandari 25

Backup & Recovery - Job scheduling crontab entries from Dell Power. Edge 2550 for oracle user 04 08 * * * /usr/local/oracle/scripts/nightly_oracle_exports. ksh 3 2>/dev/null 08 16 * * * /usr/local/oracle/scripts/coldbackup. sql 3 2>/dev/null 45 23 * * * sqlplus system/manager @/usr/local/oracle/scripts/hotbackup. sql 18 * * /usr/local/oracle/scripts/arch_compress. sh 2>/dev/null 3/6/2021 Rajashekhar Bandari 26

UCCS Admission review Application 3/6/2021 Rajashekhar Bandari 27

UCCS Admission review Application (contd. ) Logical Database(Normalized) for PHD Review Form : http: //cs. uccs. edu/~gsc/Ph. DReview. Form. htm REM * SQL DDL statements for creating various tables requied by PHD Review forms DROP TABLE PHD_REVIEW ; CREATE TABLE PHD_REVIEW (SSN VARCHAR 2(11) CONSTRAINT PHD_REVIEW_PK PRIMARY KEY, APPL_LNAME VARCHAR 2(20), APPL_FIRST_MIDDLE VARCHAR 2(30), PROGRAM_APPLIED VARCHAR 2(5), CITIZENSHIP VARCHAR 2(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_LOGIN 1 VARCHAR 2(10), COMMITTEE_LOGIN 2 VARCHAR 2(10), COMMITTEE_LOGIN 3 VARCHAR 2(10), ADM_STATUS VARCHAR 2(15)) ; 3/6/2021 Rajashekhar Bandari 28

UCCS Admission review Application (contd. ) PHD Review Form : http: //cs. uccs. edu/~gsc/Ph. DReview. Form. 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 VARCHAR 2(20), PASSWORD VARCHAR 2(10), APPLICANT_SSN VARCHAR 2(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 VARCHAR 2(2) CHECK (SUGGESTION IN ('A', 'R', 'PA')), PROF_COMMENT VARCHAR 2(2000)) ; 3/6/2021 Rajashekhar Bandari 29

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_STATUS=NULL OR ADM_STATUS='UNDER PROCESS'; TCNT NUMBER(2); BEGIN OPEN APPL_COMM_CUR; FOR I IN APPL_COMM_CUR LOOP SELECT COUNT(*) INTO TCNT FROM PHD_COMM_EVALUATION WHERE APPL_SSN=I. APPL_SSN; IF TCNT=3 THEN SELECT COUNT(*) INTO CNT FROM PHD_COMM_ EVALUATION WHERE APPL_SSN=I. APPL_SSN AND SUGGESTION IN ('ACCEPTED', 'PROVISIONALLY ACCEPTED'); IF CNT=3 THEN INSERT INTO PHD_REVIEW (ADM_STATUS) VALUES ('ACCEPTED') WHERE APPL_SSN=I. APPL_SSN; ELSIF INSERT INTO PHD_REVIEW (ADM_STATUS) VALUES ('REJECTED') WHERE APPL_SSN=I. APPL_SSN; END IF; ELSE INSERT INTO PHD_REVIEW (ADM_STATUS) VALUES ('UNDER PROCESS') WHERE APPL_SSN=I. APPL_SSN; END IF; END LOOP; END / 3/6/2021 Rajashekhar Bandari 30

Future work for web module n 3/6/2021 Oracle HTTP or Apache with Tomcat has to be installed and configured for accessing Oracle 9 i Database thru JSP’s. Rajashekhar Bandari 31

Conclusion n n Red Hat Linux 8 is Installed on Dell Power. Edge 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 9 i 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. 3/6/2021 Rajashekhar Bandari 32

Future work n n 3/6/2021 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. Rajashekhar Bandari 33

Questions? 3/6/2021 Rajashekhar Bandari 34

References n n n n n 1. Oracle 9 i: The Complete Reference (Oracle Press/Osborne) 2. Oracle 9 i Release 1 (9. 0. 1) for Linux Intel, Part No. A 90352 -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 9 i: SQL, PL/SQL, and SQL*Plus from elementk by George Callaway. 5. Oracle 9 i OCP Exam: Fundamentals I - Jason Couchman & Sudhir Marisetti from Oracle Press/Osborne. 6. Learning bash Shell, 2 nd Edition - Cameron Newham & Bill Rosenblatt from O’Reilly publishers. 7. OCP Oracle 9 i 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, c 2002. 10. Core servlets and Java. Server Pages by Marty Hall, Upper Saddle River, NJ : Prentice Hall PTR, c 2000 11. Thinking in Java by Bruce Eckel Upper Saddle River, N. J. : Prentice Hall, c 1998. 12. Database programming with JDBC and Java by George Reese. 13. Discovering HTML 4 by Bryan Pfaffenberger. 3/6/2021 Rajashekhar Bandari 35

Thanks! to my Advisor & committee Professors for their constant support & encouragement!!! 3/6/2021 Rajashekhar Bandari 36

UCCS Admission review Application Phase-1 3/6/2021 Rajashekhar Bandari 37

UCCS Admission review Application Phase-2 3/6/2021 Rajashekhar Bandari 38

UCCS Admission review Application Phase-3 3/6/2021 Rajashekhar Bandari 39