1 / 39

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

This talk discusses the design and creation of an Oracle 9i R2 database on Red Hat Linux, as well as the development of a backup and recovery tool using bash scripting. The speaker also shares their experience with porting Oracle on Linux and outlines future work.

dbaez
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related