Pennsylvania Banner Users Group 2008 Fall Conference - PowerPoint PPT Presentation

niveditha
pennsylvania banner users group 2008 fall conference l.
Skip this Video
Loading SlideShow in 5 Seconds..
Pennsylvania Banner Users Group 2008 Fall Conference PowerPoint Presentation
Download Presentation
Pennsylvania Banner Users Group 2008 Fall Conference

play fullscreen
1 / 44
Download Presentation
Pennsylvania Banner Users Group 2008 Fall Conference
823 Views
Download Presentation

Pennsylvania Banner Users Group 2008 Fall Conference

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Pennsylvania Banner Users Group 2008 Fall Conference When the moment is right will you be ready for Banner 8

  2. General Announcements: • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session • Questions will be answered ….. Thank you for your cooperation

  3. Lehigh Carbon Community College L C C C • Banner users since 2000 • General/Finance/Finance SS/FinAid/HR/Student/Student SS/FacAdv SS/AR/General • WebTailor/WebGeneral/PosControl/Empl SS/Advancement/Document Management • Application Support Services • Director of Application Support Services Jane Gilbert • Banner Technical Supervisor/unix/oracle admin Don Thaler • Banner Technical Analyst Wayne Youngblood • Banner Support Analyst David Slater • Banner Support Analyst TBA • Hardware • Dual HP RX 6600’s • Apollo Production Unix 11.23 (Database and Banner) • Zeus Backup HP Virtual Machine • Artemis…backup server for banner • Venus …backup server for AppServer(Banforms) • LCC-STOR ..windows 2003 64bit file server • Windows 2 Windows 2003 Available • Windows 3 Windows 2003 Available • HP San Array 32 72gb drives • 7 300gb drive • Banforms INB server Windows 2003 • Bannerweb SSB server Windows 2000

  4. WHY • Why Are We Changing Our Character Set? • You have to, required for Banner 8 to support the globalization of Banner • Oracle is moving away from single byte charactersets like WE8ISO8859P1 to unicode charactersets AL32UTF8. • You have to …

  5. Converting to AL32UTF8 • CSMINST .. creates tables required by csscan • CSSCAN .. Analyzes your current database and • identifies 4 types of data • Lossy…cannot be translated to AL32UTF8 • must convert to WE8MSWIN1252 first • Truncation … data that won’t fit in the specified column length when expanding from your current characterset to AL32UTF8.. fixed by setting nls_length_semantics=CHAR in the banner 8 database • Convertible.. Data that can be converted to AL32UTF8 by using • CSALTER or DATAPUMP export/import • Changeless... Data remains the same

  6. International Components for Unicode Installation • RE: Banner General 8.0 Pre-Installation Guide • Things you were dying to know: • What is ICU…is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software applications.. http://icu-project.org/download/ • Download and untar in /usr/local/include • What is GNU (Gee Not Unix) .. Unix-like operating system which is free softwareftp://ftp.gnu.org/gnu/make/ for HP users http://hpux.connect.org.uk/hppd/hpux/Gnu/make-3.8 install using swinstall creates gmake in /usr/local/bin/ • Download msgfmt.h from sghe website (faq 1-3GODR4) and place in /usr/local/include/unicode

  7. International Components for Unicode Installation (cont) • Run runConfigureICU from /usr/local/include (chmod +x create executable) • runConfigureICU… creates an ICU configuration for your server • Add /usr/local/bin to PATH • Run gmake clean • Run gmake • Run gmake install >install.log • You can run everything up to this point on your current Banner 7 installation

  8. New Compile Environment • *** the following files were downloaded from the SGHE customer support center • (1) sctproc8_hp_ux.mk • (2) beta tmclib_hp_ux.cpp (this becomes tmcilib.cpp ) in banner_home/general/c • (3) beta tmcilib_hp_ux.h (this becomes tmcilib.h ) in banner_home /general/c • (4) copy the 3 files to your general 8 install directory because the genmigr script will overlay them

  9. Creating the AL32UTF8 Database • DataPump export (expdp)/import(impdp) • Using sql/toad: create directory export_dir1 as ‘/u04/export’ on your banner 7 database when youre banner 8 database is created do the same: create directory pdump_dir1 as ‘/u04/export’ • Export your banner 7 database • Create a banner8 instance specifing nls_characterset = AL32UTF8 and nls_length_semantics=CHAR • Apply oracle patches 5874989 and 5875568 • Import the banner7 database into banner8

  10. Additional Requirements • Banenv file should contain reference to ICU_HOME • Faq 1-kpf81..how to maintain banner 7 function keys • Your .env file on your inb server should have an entry:nls_lang=AMERICAN_AMERICA.AL32UTF8 • When creating the dad for banner8 on your inb server make sure the character set is set to AMERICAN_AMERICA.AL32UTF8 • The forms compile shells must contain an entry: nls_length_semantics=char export nls_length_semantics

  11. Additional Requirements • If banner8 and banner7 coexist on same server you can’t use the global etc/profile for storing the new locale variables. I Modified oraenv: if [ "${ORACLE_SID}" = "BAN80" ] then#ICU additions • LANG=en.US.UTF-8 etc…. • If you use evisions you need a new eviadm file and banner 8 compatible programs i.e. phpchkl,fabchkp

  12. Banner 8 Conversion Diagram

  13. Top 10 signs you know when you’ve been thru a banner 8 upgrade • You count gnu’s instead of sheep • You see dancing unicodes • AL 32 UTF 8 is not a football score • Your’re on a first name basis with Scott Hardin/Colin Abbot/ Charles Shultz (University Of Illinois Banner 8 Character Set presenters) • You’ve got a forest of paper from downloading the oracle notes on character conversion. • When someone tells you you have a ‘lousy’ attititude your response is ‘up your csscan’ • CSS no longer reminds you of a TV show • You think ICU is a recursive acronym for ‘ I see you ‘ • When you hear the Cialis commercial ‘when the time is right will you be ready’ you think of Banner 8 • You get email with a return url of WAL*MART =?UTF8?Q?=24?=500 Gift Card is Inside

  14. Here’s…………Charlie

  15. LCCC Banner 8 Upgrade • A. CSSCAN...Character Set Scan Utility (command line utility) • 1. Make sure LD_LIBRARY_PATH = $OH/lib:/usr/dt/lib:/usr/lib • SHLIB_PATH = $OH/lib:/usr/dt/lib:/usr/lib • 2. chmod 755 /$OH/lib/libclntsh.so.10.1 • 3. cd /$OH/rdbms/admin • 4. sqlplus /nolog • connect /as sysdba • @csminst.sql... (creates user 'csmig' owner of tables and procedures for the CSSCAN) • exit; • 5. csscan fromchar=WE8ISO8859P1 tochar=WE8ISO8859P1 log=WE8check capture=y array=1000000 process=2 ( run as sys ) • 6. check output files WE8check.out (log file) WE8check.txt (look at the data under the columns changeless,convertible,truncation, lousy) If all data is not reported as 'CHANGELESS' see oracle note:260192.1 WE8check.err (exceptions) • 7. for additional information on CSSCAN see http://articles.techrepublic.com.com/5100-22_11-5219084.html

  16. LCCC Banner 8 Upgrade • B. ICU installation: (see Banner General 8.0 Pre_Installation Guide April 2008) Install International Components for Unicode (ICU) • (these are peculiar to HP UNIX, most of the faq's have sections on solaris/linux) • (1)downloaded GNU (make-3.81-ia64-11.23.depot), which i got from the HP website (http://hpux.connect.org.uk/hppd/hpux/Gnu/make-3.81) and installed using swinstall • (2) downloaded the ICU (icu4c-3_6-src.tar), from http://icu-project.org/download/3.6html, identified in the Banner General Pre_Installation Guide • (3) tar'd the icu4c-3_6-src.tar into /usr/local/include , this will become $ICU_HOME, the installation process(gmake) places the ICU files in /usr/local/lib • (5) download msgfmt.h from sungard website (see faq 1-3GODR4) place in /usr/local/include/unicode and /usr/local/include/icu/source/i18n/Unicode • note: sghe recommends files be copied as zipped files and unzipped on the server • (6) cd /usr/local/include • chmod (755) or +x /usr/local/include/runConfigureICU and then run runConfigureICu HP-UX/ACC (see faq1-3GODR4) • runConfigureICU --help lists the options for each of the platforms supported • (the default install is 64bit, for 32bit .runConfigureICU –disable-64-bit-libs) • (7) cd /usr/local/include/icu/source... • Add the home for gmake (whereis gmake) to your path statement ( e.g PATH=$PATH:/usr/local/bin ) • Run gmake clean • Run gmake • Run gmake install > install.log (hope murphy isn't around) • NOTE: everything prior can be run on your production system without affecting the current Banner 7 installation

  17. C. New compile script data • *** the following files were downloaded from the SGHE customer support center • (1) sctproc8_hp_ux.mk (from sungard customer support )... save as sctproc.mk and make • any changes using your existing sctproc.mk file (save in gen80000u directory because the genmigr process will overlay the current one with this one • (1a) as a result of getting this from sungard there was no need to run the buildmk process so they said • see defect 1-3qO4RX for info on the following files • (2) download beta tmclib_hp_ux.cpp (this becomes tmcilib.cpp ) in banner_home/general/c • (3) download beta tmcilib_hp_ux.h (this becomes tmcilib.h ) in banner_home /general/c • (4) see faq 1-3GODR4 ..."edit the global /etc/profile...." the variables referenced can be placed in the /etc/profile if the • server that is running banner8 doesn't also have a banner 7 instance, otherwise you need to make the changes to the oracle and banner users profile when they're accessing the banner 8 system.. i changed the oraenv file and added the following so that the variables are set when the oracle user or banner user selects the banner 8 sid BAN80: • if [ "${ORACLE_SID}" = "BAN80" ] then#ICU additions#---------------------------------------------------------------------------------LANG=en_US.UTF-8LC_ALL=en_US.UTF-8LC_COLLATE=en_US.UTF-8LC_CTYPE=en_US.UTF-8LC_MESSAGES=en_US.UTF-8LC_MONETARY=en_US.UTF-8LC_NUMERIC=en_US.UTF-8LC_TIME=en_US.UTF-8export LANG LC_ALL LC_COLLATE LC_CTYPE LC_MESSAGES LC_MONETARY LC_NUMERIC LC_TIME • (5) The new banenv file contains references for LD_LIBRARY_PATH and ICU_HOME • (6) faq's referenced: 1-3GODR4,1-3QO4RX • (7) if you are using evisions you will need to contact them for a new eviadm file, when compiling eviadm make sure there is no eviadm.c file present • (9) check faq #1-kpf8l which describes how to maintain the banner 7 function keys • (10) there must be an entry in your .env file on your application server for NLS_LANG=AMERICAN_AMERICA.AL32UTF8 • (11) when creating the dad for banner 8 make sure the character set is set to AMERICAN_AMERICA.AL32UTF8 • (12) the forms compile shell must contain an entry for NLS_LENGTH_SEMANTICS=CHAR export NLS_LENGTH_SEMANTICS • (most of the ones delivered by sghe do have above settings) • (13) while testing some of the processes that run thru jobsubmission, like 'farinvs' , we found the $ in the output file was being replaced by some weird character, we had to set the locale (LC) variables to a null value i.e. LANG=; LC_ALL=; etc. (defect 1-3VXNAY )

  18. LCCC Banner 8 Upgrade • D. Database export/import information for data pump • (1) create directory on your banner 7 and banner 8 databases: • banner7 : using sql create directory export_dir1 as '/u04/export' • banner8 : using sql create directory dpump_dir1 as '/u04/export' • (2) export using datapump: expdp system/password parfile=/u04/export/expdp.par logfile=LCCC_export.log (attachment 1) • (3) import using datapump: impdp system/password parfile=/u04/export/full_import.par logfile=BAN80_import.log (attachment 2) • E. DB Creation • (1) create banner8 database with NLS_CHARACTERSET = AL32UTF8 (this is the database character set) • (2) before importing the banner 7 db into the banner 8 db set nls_length_semantics=char • ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH • (3) apply patches 5874989 and 5875568 • (4) see attachement 4 for the scripts I used to create my banner 8 database, created by running dbua and saving scripts.

  19. LCCC Banner 8 Upgrade • F. Creating your production banner 8 instance from your banner 8 test instance. • See (http://www.uaex.edu/srea/RMAN_Scripts_and_Tips.htm) Steve Rea (Univ Of Arkansas) • (1) Run rmanbackup.shl to create a backup of your banner 8 test instance. • (2) Run rmanclone.shl to copy your banner 8 test instance over to your banner production instance. (this step drops the production db and recreates it) • (3) Copy your banner 8 test directories to your banner 8 production directories (this assumes your banner 8 test directories were created from you banner 7 directories) • a. change the directory to /u01/app/sghe/banLCCC (banner prouduction) and execute the command rm -rf /u01/app/sghe/banLCCC (this removes all the directories and sub directories) • b.change directory to /u01/app/sghe/banTEST (home for banner 8 test) and execute: find . -print|cpio -pudmv /u01/app/sghe/banLCCC • (this copies all the directories and sub directories from banTEST to banLCCC(production banner 8). • c.change the banenv file thats located in • /u01/app/sghe/banLCCC/admin because its entries point to the banner TEST home. • (4) If your Test directories are not on the same mount point as your production directories e.g TEST = /u04/app/sghe/banTEST and production is /u02/app/sghe/banLCCC then you will need to delete the $BANNER_LINKS directory and rebuild it using banlink.shl or banlnks.shl found in the $BANNER_HOME directory • (5) Run the Banner 8 upgrade scripts skipping the c /cobol compiles, gurutlrp • and migration Steps. • (6) Copy the .fmb and .fmx files from your banner 8 test environment to your production environment (no need to recompile as long as they both use the same seed nrs)

  20. LCCC Banner 8 Upgrade • G. Interesting facts • (1) The database character set is the character set of CHAR, VARCHAR2, LONG, and CLOB data stored in the database columns, and of SQL and PL/SQL text stored in the Data Dictionary. • The national character set is the character set of NCHAR, NVARCHAR2, and NCLOB data types. Banner does not use these datatypes. • (2) (from metalink) If you do *not* use N-types then there is NO problem at all with NLS_NCHAR_CHARACTERSET=AL16UTF16 and we strongly advice you to keep NLS_NCHAR_CHARACTERSET = AL16UTF16 • (3) to determine if you have N-type columns? • select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB'); • (4) If you have only the SYS / SYSTEM tables listed in point ( 3) then you don't have USER data using N-type columns. • Your database will just have the -default- AL16UTF16 NLS_NCHAR_CHARACTERSET declaration after the upgrade and the standard sys/system tables. • See Metalink note Note:276914.1 The National Character Set in Oracle 9i and 10g • A select from v$parameters or nls_instance_parameters shows the value of nls_length_semantics at the moment the d.b was started. • A select form nls_database_parameters shows the value when the d.b was created • (5) The name “GNU” is a recursive acronym for “GNU's Not Unix”; it is pronounced g-noo, as one syllable with no vowel sound between the g and the n. • (6) A recursive acronym (or occasionally recursive initialism, and sometimes recursive backronym) is an abbreviation that refers to itself in the expression for which it stands. (one nation under god) • (7) A backronym (or bacronym) is a phrase that is constructed "after the fact" from a previously existing word or abbreviation, the abbreviation being an initialism or an acronym. Backronyms may be invented with humorous intent, or may be a type of false or folk etymology. • (8) The word is a portmanteau term combining back and acronym, coined in 1983[1] and documented from 1994:

  21. LCCC Banner 8 Upgrade • Top 10 signs you know when you’ve been thru a banner 8 upgrade; • You count gnu’s instead of sheep • You see dancing unicodes • AL 32 UTF 8 is not a football score • Your’re on a first name basis with Scott Hardin/Colin Abbot/ Charles Shultz (University Of Illinois Banner 8 Character Set presenters) • You’ve got a forest of paper from downloading the oracle notes on character conversion. • When someone tells you you have a ‘lousy’ attititude your response is ‘up your csscan’ • CSS no longer reminds you of a TV show • You think ICU is an acronym for ‘ I see you ‘ • When you hear the Cialis commercial ‘when the time is right will you be ready’ you think of Banner 8 • You get email with a return url of WAL*MART =?UTF8?Q?=24?=500 Gift Card is Inside

  22. LCCC Banner 8 Upgrade • Additional references: • http://aprilcsims.wordpress.com/2008/07/10/migrating-to-al32utf8-on-10gr2-for-banner/ • http://www.mcgill.ca/isr-dba/presentations/ under Sungard Summit

  23. LCCC Banner 8 Upgrade • Expdp.par • directory=export_dir1 • dumpfile=expdp_full_lccc.dmp • full=Y • Impdp systen/xxxxx full=y parfile=/u04/export/full_import.par logfile=ban80_import.log • Full_import.par • directory=dpump_dir1 • dumpfile=expdp_full_lccc.dmp • REMAP_DATAFILE=/u02/oradata/LCCC/banaq_LCCC_01.dbf:/u02/oradata/JANE/banaq_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/banlob_LCCC_01.dbf:/u02/oradata/JANE/banlob_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/cntrl_LCCC_01.ctl:/u02/oradata/JANE/cntrl_JANE_01.ctl • REMAP_DATAFILE=/u02/oradata/LCCC/cntrl_LCCC_02.ctl:/u02/oradata/JANE/cntrl_JANE_01.ctl • REMAP_DATAFILE=/u02/oradata/LCCC/cntrl_LCCC_03.ctl:/u02/oradata/JANE/cntrl_JANE_01.ctl • REMAP_DATAFILE=/u02/oradata/LCCC/devl_LCCC_01.dbf://u02/oradata/JANE/devl_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devl_LCCC_02.dbf:/u02/oradata/JANE/devl_JANE_02.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devl_LCCC_03.dbf:/u02/oradata/JANE/devl_JANE_03.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devl_LCCC_04.dbf:/u02/oradata/JANE/devl_JANE_04.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devl_LCCC_05.dbf:/u02/oradata/JANE/devl_JANE_05.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devlindx_LCCC_01.dbf:/u02/oradata/JANE/devlindx_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devlindx_LCCC_02.dbf:/u02/oradata/JANE/devlindx_JANE_02.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devlindx_LCCC_03.dbf:/u02/oradata/JANE/devlindx_JANE_03.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/devlindx_LCCC_04.dbf:/u02/oradata/JANE/devlindx_JANE_04.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/rbs_LCCC_01.dbf:/u02/oradata/JANE/rbs_JANE_01.dbf

  24. LCCC Banner 8 Upgrade • REMAP_DATAFILE=/u02/oradata/LCCC/redo_LCCC_01a.log:/u02/oradata/JANE/redo_JANE_01a.log • REMAP_DATAFILE=/u02/oradata/LCCC/redo_LCCC_01b.log:/u02/oradata/JANE/redo_JANE_01b.log • REMAP_DATAFILE=/u02/oradata/LCCC/redo_LCCC_02a.log:/u02/oradata/JANE/redo_JANE_02a.log • REMAP_DATAFILE=/u02/oradata/LCCC/redo_LCCC_02b.log:/u02/oradata/JANE/redo_JANE_02b.log • REMAP_DATAFILE=/u02/oradata/LCCC/redo_LCCC_03a.log:/u02/oradata/JANE/redo_JANE_03a.log • REMAP_DATAFILE=/u02/oradata/LCCC/redo_LCCC_03b.log:/u02/oradata/JANE/redo_JANE_03b.log • REMAP_DATAFILE=/u02/oradata/LCCC/sysaux_LCCC_01.dbf:/u02/oradata/JANE/sysaux_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/system_LCCC_01.dbf:/u02/oradata/JANE/system_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/temp_LCCC_01.dbf:/u02/oradata/JANE/temp_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/tools_LCCC_01.dbf:/u02/oradata/JANE/tools_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/undotbs_LCCC_01.dbf:/u02/oradata/JANE/undotbs_JANE_01.dbf • REMAP_DATAFILE=/u02/oradata/LCCC/users_LCCC_01.dbf:/u02/oradata/JANE/users_JANE_01.dbf

  25. LCCC Banner 8 Upgrade • BAN80.sh • #!/bin/sh • #mkdir -p /u01/app/oracle/admin/BAN80/adump • #mkdir -p /u01/app/oracle/admin/BAN80/bdump • #mkdir -p /u01/app/oracle/admin/BAN80/cdump • #mkdir -p /u01/app/oracle/admin/BAN80/dpdump • #mkdir -p /u01/app/oracle/admin/BAN80/pfile • #mkdir -p /u01/app/oracle/admin/BAN80/udump • #mkdir -p /u01/app/oracle/product/10.2/cfgtoollogs/dbca/BAN80 • #mkdir -p /u01/app/oracle/product/10.2/dbs • #mkdir -p /u02/oradata/BAN80 • ORACLE_SID=BAN80; export ORACLE_SID • echo You should Add this entry in the /etc/oratab: BAN80:/u01/app/oracle/product/10.2.0:Y • /u01/app/oracle/product/10.2/bin/sqlplus /nolog @/u01/app/oracle/admin/BAN80/scripts/BAN80_CREATE_DB.sql

  26. LCCC Banner 8 Upgrade • BAN80_CREATE_DB.sql • set verify off • PROMPT specify a password for sys as parameter 1; • DEFINE sysPassword = &1 • PROMPT specify a password for system as parameter 2; • DEFINE systemPassword = &2 • PROMPT specify a password for sysman as parameter 3; • DEFINE sysmanPassword = &3 • PROMPT specify a password for dbsnmp as parameter 4; • DEFINE dbsnmpPassword = &4 • host /u01/app/oracle/product/10.2/bin/orapwd file=/u01/app/oracle/product/10.2/dbs/orapwBAN80 password=&&sysPassword force=y • @/u01/app/oracle/admin/BAN80/scripts/CloneRmanRestore.sql • @/u01/app/oracle/admin/BAN80/scripts/cloneDBCreation.sql • @/u01/app/oracle/admin/BAN80/scripts/postScripts.sql • @/u01/app/oracle/admin/BAN80/scripts/postDBCreation.sql

  27. LCCC Banner 8 Upgrade • CloneRmanRestore.sql • connect "SYS"/"&&sysPassword" as SYSDBA • set echo on • spool /u01/app/oracle/admin/BAN80/scripts/CloneRmanRestore.log • startup nomount pfile="/u01/app/oracle/admin/BAN80/scripts/init.ora"; • @/u01/app/oracle/admin/BAN80/scripts/rmanRestoreDatafiles.sql;

  28. LCCC Banner 8 Upgrade • Init.ora • ############################################################################## • # Copyright (c) 1991, 2001, 2002 by Oracle Corporation • ############################################################################## • ########################################### • # Cache and I/O • ########################################### • db_block_size=8192 • db_file_multiblock_read_count=16 • ########################################### • # Cursors and Library Cache • ########################################### • open_cursors=300 • ########################################### • # Database Identification • ########################################### • db_domain=artemis.lccc.edu • db_name=BAN80 • ########################################### • # Diagnostics and Statistics • ########################################### • background_dump_dest=/u01/app/oracle/admin/BAN80/bdump • core_dump_dest=/u01/app/oracle/admin/BAN880/cdump • user_dump_dest=/u01/app/oracle/admin/BAN80/udump • ########################################### • # File Configuration • ########################################### • control_files=("/u02/oradata/BAN80/cntrl_BAN80_01.ctl", "/u02/oradata/BAN80/cntrl_BAN80_02.ctl", "/u02/oradata/BAN80/cntrl_BAN80_03.ctl")

  29. LCCC Banner 8 Upgrade • ########################################### • # Job Queues • ########################################### • job_queue_processes=10 • ########################################### • # Miscellaneous • ########################################### • compatible=10.2.0.3.0 • ########################################### • # Processes and Sessions • ########################################### • processes=150 • ########################################### • # SGA Memory • ########################################### • sga_target=1073741824 • ########################################### • # Security and Auditing • ########################################### • audit_file_dest=/u01/app/oracle/admin/BAN80/adump • remote_login_passwordfile=EXCLUSIVE • ########################################### • # Shared Server • ########################################### • dispatchers="(PROTOCOL=TCP) (SERVICE=BAN80XDB)" • ########################################### • # Sort, Hash Joins, Bitmap Indexes • ########################################### • pga_aggregate_target=268435456 • ########################################### • # System Managed Undo and Rollback Segments • ########################################### • undo_management=AUTO • undo_tablespace=UNDOTBS1

  30. LCCC Banner 8 Upgrade • RmanRestoreDatafiles.sql • set echo off; • set serveroutput on; • select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual; • variable devicename varchar2(255); • declare • omfname varchar2(512) := NULL; • done boolean; • begin • dbms_output.put_line(' '); • dbms_output.put_line(' Allocating device.... '); • dbms_output.put_line(' Specifying datafiles... '); • :devicename := dbms_backup_restore.deviceAllocate; • dbms_output.put_line(' Specifing datafiles... '); • dbms_backup_restore.restoreSetDataFile; • dbms_backup_restore.restoreDataFileTo(1, '/u02/oradata/BAN80/system_BAN80_01.dbf', 0, 'SYSTEM'); • dbms_backup_restore.restoreDataFileTo(2, '/u02/oradata/BAN80/undotbs_BAN80_01.dbf', 0, 'UNDOTBS1'); • dbms_backup_restore.restoreDataFileTo(3, '/u02/oradata/BAN80/sysaux_BAN80_01.dbf', 0, 'SYSAUX'); • dbms_backup_restore.restoreDataFileTo(4, '/u02/oradata/BAN80/users_BAN80_01.dbf', 0, 'USERS'); • dbms_output.put_line(' Restoring ... '); • dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/10.2/assistants/dbca/templates/Seed_Database.dfb', done); • if done then • dbms_output.put_line(' Restore done.'); • else • dbms_output.put_line(' ORA-XXXX: Restore failed '); • end if; • dbms_backup_restore.deviceDeallocate; • end; • / • select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;

  31. LCCC Banner 8 Upgrade • CloneDBCreation.sql • connect "SYS"/"&&sysPassword" as SYSDBA • set echo on • spool /u01/app/oracle/admin/BAN80/scripts/cloneDBCreation.log • Create controlfile reuse set database "BAN80" • MAXINSTANCES 8 • MAXLOGHISTORY 1 • MAXLOGFILES 16 • MAXLOGMEMBERS 3 • MAXDATAFILES 100 • Datafile • '/u02/oradata/BAN80/system_BAN80_01.dbf', • '/u02/oradata/BAN80/undotbs_BAN80_01.dbf', • '/u02/oradata/BAN80/sysaux_BAN80_01.dbf', • '/u02/oradata/BAN80/users_BAN80_01.dbf' • LOGFILE GROUP 1 ('/u02/oradata/BAN80/redo_BAN80_01a.log','/u02/oradata/BAN80/redo_BAN80_01b.log') SIZE 6M, • GROUP 2 ('/u02/oradata/BAN80/redo_BAN80_02a.log','/u02/oradata/BAN80/redo_BAN80_02b') SIZE 6M, • GROUP 3 ('/u02/oradata/BAN80/redo_BAN80_03a.log','/u02/oradata/BAN80/redo_BAN80_03b.log') SIZE 6M RESETLOGS; • exec dbms_backup_restore.zerodbid(0); • shutdown immediate; • startup nomount pfile="/u01/app/oracle/admin/BAN80/scripts/initBAN80Temp.ora"; • Create controlfile reuse set database "BAN80" • MAXINSTANCES 8 • MAXLOGHISTORY 1 • MAXLOGFILES 16 • MAXLOGMEMBERS 3 • MAXDATAFILES 100

  32. LCCC Banner 8 Upgrade • Datafile • '/u02/oradata/BAN80/system_BAN80_01.dbf', • '/u02/oradata/BAN80/undotbs_BAN80_01.dbf', • '/u02/oradata/BAN80/sysaux_BAN80_01.dbf', • '/u02/oradata/BAN80/users_BAN80_01.dbf' • LOGFILE GROUP 1 ('/u02/oradata/BAN80/redo_BAN80_01a.log','/u02/oradata/BAN80/redo_BAN80_01b.log') SIZE 6M, • GROUP 2 ('/u02/oradata/BAN80/redo_BAN80_02a.log','/u02/oradata/BAN80/redo_BAN80_02b.log') SIZE 6M, • GROUP 3 ('/u02/oradata/BAN80/redo_BAN80_03a.log','/u02/oradata/BAN80/redo_BAN80_03b.log') SIZE 6M RESETLOGS; • alter system enable restricted session; • alter database "BAN80" open resetlogs; • alter database rename global_name to "BAN80.artemis.lccc.edu"; • ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/BAN80/temp_BAN80_01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; • select tablespace_name from dba_tablespaces where tablespace_name='USERS'; • select sid, program, serial#, username from v$session; • alter database character set INTERNAL_CONVERT AL32UTF8; • alter database national character set INTERNAL_CONVERT AL16UTF16; • alter user sys identified by "&&sysPassword"; • alter user system identified by "&&systemPassword"; • alter system disable restricted session;

  33. LCCC Banner 8 Upgrade • InitBan80Temp.ora • ############################################################################## • # Copyright (c) 1991, 2001, 2002 by Oracle Corporation • ############################################################################## • ########################################### • # Cache and I/O • ########################################### • db_block_size=8192 • db_file_multiblock_read_count=16 • ########################################### • # Cursors and Library Cache • ########################################### • open_cursors=300 • ########################################### • # Database Identification • ########################################### • db_domain=artemis.lccc.edu • db_name=BAN80 • ########################################### • # Diagnostics and Statistics • ########################################### • background_dump_dest=/u01/app/oracle/admin/BAN80/bdump • core_dump_dest=/u01/app/oracle/admin/BAN80/cdump • user_dump_dest=/u01/app/oracle/admin/BAN80/udump • ########################################### • # File Configuration • ########################################### • control_files=("/u02/oradata/BAN80/cntrl_BAN80_01.ctl", "/u02/oradata/BAN80/cntrl_BAN80_02.ctl", "/u02/oradata/BAN80/cntrl_BAN80_03.ctl") • ########################################### • # Job Queues • ###########################################

  34. LCCC Banner 8 Upgrade • ########################################### • # Miscellaneous • ########################################### • compatible=10.2.0.3.0 • ########################################### • # Processes and Sessions • ########################################### • processes=150 • ########################################### • # SGA Memory • ########################################### • sga_target=1073741824 • ########################################### • # Security and Auditing • ########################################### • audit_file_dest=/u01/app/oracle/admin/BAN80/adump • remote_login_passwordfile=EXCLUSIVE • ########################################### • # Shared Server • ########################################### • dispatchers="(PROTOCOL=TCP) (SERVICE=BAN80XDB)" • ########################################### • # Sort, Hash Joins, Bitmap Indexes • ########################################### • pga_aggregate_target=268435456 • ########################################### • # System Managed Undo and Rollback Segments • ########################################### • undo_management=AUTO • undo_tablespace=UNDOTBS1 • _no_recovery_through_resetlogs=true

  35. LCCC Banner 8 Upgrade • postScripts.sql • connect "SYS"/"&&sysPassword" as SYSDBA • set echo on • spool /u01/app/oracle/admin/BAN80/scripts/postScripts.log • @/u01/app/oracle/product/10.2/rdbms/admin/dbmssml.sql; • execute dbms_datapump_utl.replace_default_dir; • commit; • connect "SYS"/"&&sysPassword" as SYSDBA • alter session set current_schema=ORDSYS; • @/u01/app/oracle/product/10.2/ord/im/admin/ordlib.sql; • alter session set current_schema=SYS; • connect "SYS"/"&&sysPassword" as SYSDBA • connect "SYS"/"&&sysPassword" as SYSDBA • execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE); • commit; • spool off

  36. LCCC Banner 8 Upgrade • postDBcreation.sql • connect "SYS"/"&&sysPassword" as SYSDBA • set echo on • spool /u01/app/oracle/admin/BAN80/scripts/postDBCreation.log • connect "SYS"/"&&sysPassword" as SYSDBA • set echo on • create spfile='/u01/app/oracle/product/10.2/dbs/spfileBAN80.ora' FROM pfile='/u01/app/oracle/admin/BAN80/scripts/init.ora'; • shutdown immediate; • connect "SYS"/"&&sysPassword" as SYSDBA • startup ; • alter user SYSMAN identified by "&&sysmanPassword" account unlock; • alter user DBSNMP identified by "&&dbsnmpPassword" account unlock; • select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual; • execute utl_recomp.recomp_serial(); • select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual; • host /u01/app/oracle/product/10.2/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME BAN80 -PORT 1521 -EM_HOME /u01/app/oracle/product/10.2 -LISTENER LISTENER -SERVICE_NAME BAN80.artemis.lccc.edu -SYS_PWD &&sysPassword -SID BAN80 -ORACLE_HOME /u01/app/oracle/product/10.2 -DBSNMP_PWD &&dbsnmpPassword -HOST artemis.lccc.edu -LISTENER_OH /u01/app/oracle/product/10.2 -LOG_FILE /u01/app/oracle/admin/BAN80/scripts/emConfig.log -SYSMAN_PWD &&sysmanPassword; • spool /u01/app/oracle/admin/BAN80/scripts/postDBCreation.log • exit;

  37. LCCC Banner 8 Upgrade • : • #!/bin/sh • # Name: banlink.shl • # Desc: Link all files under $BANNER_HOME • # To execute, change directory to $BANNER_HOME and type banlink.shl • # • # For symbolic links. • # LN='ln -sf' • # For hard links. • LN='ln -f' • # • # Add any new products under Banner Home to this list • # or DELETE any products which you do not want to link. • # • products='\ • admin \ • arsys \ • common \ • finance \ • genweb \ • posnctl \ • stuweb \ • alumni \ • baninas \ • facweb \ • finweb \ • install \ • payroll \

  38. LCCC Banner 8 Upgrade • scomweb \ • wtlweb \ • aluweb \ • bansso \ • finaid \ • general \ • intcomp \ • payweb \ • student \ • ' • #product_list_end • # • # • # Verify environmental variables are set • # • if [ "$BANNER_LINKS" = "" ]; then • echo "The BANNER_LINKS variable must be set"; exit 3 • fi • if [ "$BANNER_HOME" = "" ]; then • echo "The BANNER_HOME variable must be set"; exit 3 • fi • # • echo BANNER_HOME is set to $BANNER_HOME • echo BANNER_LINKS is set to $BANNER_LINKS • echo "continue ? (y/N) \c " • read ynprompt • case $ynprompt in • y|Y) • ;; • *) • echo aborting... • exit 2 ;; • esac

  39. LCCC Banner 8 Upgrade • # • # • # do special linking of copybooks from lower to upper case. • # • cd ${BANNER_HOME}/general/cob/lib • for file in *.cob ; do • base=`basename $file .cob` • upbase=`echo $base | tr "[a-z]" "[A-Z]"` • echo +++++++++ linking copybook $upbase • ${LN} $file ../$upbase • ${LN} $file ${BANNER_LINKS}/$upbase • done • # • # Link all files except executables and copybooks • # • for prod in $products ; do • if [ -d ${BANNER_HOME}/$prod ] ; then • echo ++++++++ linking $prod • cd ${BANNER_HOME}/${prod} • find . \( -name 'lib' -o -name 'exe' \) -prune -o -type f -print • -exec ${LN} {} ${BANNER_LINKS} \; • fi • done

  40. Output Of Csscan FROMCHAR = WE8ISO8859P1 TOCHAR= AL32UTF8 • Database Scan Summary Report • Time Started : 2008-05-28 10:08:29 • Time Completed: 2008-05-28 10:22:12 • Process ID Time Started Time Completed • [Database Size] • Tablespace Used Free Total Expansion • ------------------------- --------------- --------------- --------------- --------------- • SYSTEM 852.19M 127.81M 980.00M 688.00K • UNDOTBS1 14.81M 415.19M 430.00M .00K • SYSAUX 258.25M 1.75M 260.00M 11.46M • TEMP .00K .00K .00K .00K • USERS 1.81M 3.19M 5.00M .00K • DEVELOPMENT 12,652.06M 7,827.94M 20,480.00M 1.80M • DEVELOPMENT_NDX 5,073.75M 11,310.25M 16,384.00M .00K • RBS 64.00K 1,535.94M 1,536.00M .00K • TOOLS 2.13M 125.88M 128.00M .00K • BANAQ 1.94M 62.06M 64.00M .00K • BANLOB 384.00K 255.63M 256.00M .00K • ------------------------- --------------- --------------- --------------- --------------- • Total 18,857.38M 21,665.63M 40,523.00M 13.94M • The size of the largest CLOB is 1625114 bytes

  41. Output Of Csscan FROMCHAR = WE8ISO8859P1 TOCHAR= AL32UTF8 • [Scan Summary] • All character type data in the data dictionary are convertible to the new character set • All character type application data are convertible to the new character set • [Data Dictionary Conversion Summary] • Datatype Changeless Convertible Truncation Lossy • --------------------- ---------------- ---------------- ---------------- ---------------- • VARCHAR2 6,110,339 7 0 0 • CHAR 3,221 0 0 0 • LONG 405,454 0 0 0 • CLOB 25,598 1,443 0 0 • VARRAY 23,466 0 0 0 • --------------------- ---------------- ---------------- ---------------- ---------------- • Total 6,568,078 1,450 0 0 • Total in percentage 99.978% 0.022% 0.000% 0.000% • The data dictionary can not be safely migrated using the CSALTER script

  42. Output Of Csscan FROMCHAR = WE8ISO8859P1 TOCHAR= AL32UTF8 • [Application Data Conversion Summary] • Datatype Changeless Convertible Truncation Lossy • --------------------- ---------------- ---------------- ---------------- ---------------- • VARCHAR2 1,290,376,888 25 0 0 • CHAR 2,300,672 0 0 0 • LONG 121,375 0 0 0 • CLOB 432,200 6,507 0 0 • VARRAY 1,519 0 0 0 • --------------------- ---------------- ---------------- ---------------- ---------------- • Total 1,293,232,654 6,532 0 0 • Total in percentage 99.999% 0.001% 0.000% 0.000% • [Distribution of Convertible, Truncated and Lossy Data by Table]

  43. Open to the Floor • Questions • Comments