1 / 44

Pennsylvania Banner Users Group 2008 Fall Conference

Pennsylvania Banner Users Group 2008 Fall Conference When the moment is right will you be ready for Banner 8 General Announcements: Please turn off all cell phones/pagers If you must leave the session early, please do so as discreetly as possible

niveditha
Download Presentation

Pennsylvania Banner Users Group 2008 Fall Conference

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

More Related