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

1. Oracle 9i R2 Database Design, Backup & Recovery on Linux 8 Master’s 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 Rajashekhar Bandari

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) Rajashekhar Bandari

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) Rajashekhar Bandari

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 <lnx_920_disk1.cpio $ cpio –idmv <lnx_920_disk2.cpio $ cpio –idmv <lnx_920_disk3.cpio Rajashekhar Bandari

6. Oracle 9i R2 porting (contd.) Reboot the system inorder to load the new Kernel parameters…! # su – oracle $ cd /tmp/software/Disk1 $ ./runInstaller Rajashekhar Bandari

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 Rajashekhar Bandari

8. Oracle 9i Database Architecture Rajashekhar Bandari

9. Database design & creation a. Create Physical Database startup nomount pfile=$ORACLE_BASE/admin/UCCS/pfile/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/log1a.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; Rajashekhar Bandari

10. 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 Rajashekhar Bandari

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_admin01.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; Rajashekhar Bandari

12. Oracle 9i Database – startup/shutdown Rajashekhar Bandari

13. Oracle 9i Database – startup/shutdown Rajashekhar Bandari

14. Oracle 9i Database – STARTUP script problems • $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 Rajashekhar Bandari

15. Oracle 9i Networking • listener.ora • tnsnames.ora • sqlnet.ora Rajashekhar Bandari

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

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 Rajashekhar Bandari

18. Oracle Backup & RecoveryRecovery Manager • Needs recovery catalog to store Repository • No compression option • No tape support Rajashekhar Bandari

19. Oracle 9i Backup & Recovery – bash scripting Rajashekhar Bandari

20. Oracle 9i Backup & Recovery (contd.) • Physical Backups - cold backups - hot backups • Logical Backups • exp utility • Imp utility Rajashekhar Bandari

21. Oracle 9i Backup & Recovery (contd.) Algorithm for Hot backups: • 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

22. Problem identified Rajashekhar Bandari

23. 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 Rajashekhar Bandari

24. Hot backups & problems fixed (contd.) ALGORITHM: • 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

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 v$backup where STATUS='ACTIVE'); BEGIN OPEN C1; cursor_id := dbms_sql.open_cursor; fetch C1 into TSNAME; WHILE C1%FOUND 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; END; / Rajashekhar Bandari

26. Backup & Recovery - Job scheduling crontab entries from Dell PowerEdge 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 Rajashekhar Bandari

27. UCCS Admission review Application Rajashekhar Bandari

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), COMMITTEE_LOGIN2 VARCHAR2(10), COMMITTEE_LOGIN3 VARCHAR2(10), ADM_STATUS VARCHAR2(15)) ; Rajashekhar Bandari

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)) ; Rajashekhar Bandari

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_STATUS=NULL 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_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 / Rajashekhar Bandari

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

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. Rajashekhar Bandari

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. Rajashekhar Bandari

34. Questions? Rajashekhar Bandari

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 SQL*Plus 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 O’Reilly • 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. Rajashekhar Bandari

36. Thanks! to my Advisor & committee Professors for their constant support & encouragement!!! Rajashekhar Bandari

37. UCCS Admission review ApplicationPhase-1 Rajashekhar Bandari

38. UCCS Admission review ApplicationPhase-2 Rajashekhar Bandari

39. UCCS Admission review ApplicationPhase-3 Rajashekhar Bandari