Oracle OpenWorld 2009 Migrating to 11g – Step-Ordered Approach

Here it is folks…corrected version.

Thanks for attending my session.

2009_27_Sims_ppt_Final

 

Oracle OpenWorld Presentations

Feel free to stop and say hello and leave your business card.

Let me know if you want to write for IOUG SELECT Journal.

 

ID#: S308076
Title: Resolve Issues Faster with My Oracle Support and Oracle Enterprise Manager
Date: 15-OCT-09
Time: 10:30-11:30
Venue: Moscone South
Room: Room 307

ID#: S307607
Title: Migrating to Oracle Database 11g: Step-Ordered Approach
Date: 15-OCT-09
Time: 13:30-14:30
Venue: Moscone South
Room: Room 303

 

GC Wrongly Alerting About Data Block Corruption

Subject:	Grid Control Wrongly Alerting About Data Block Coruption Error Found In Alert Log
	Doc ID:	829066.1	Type:	PROBLEM
	Modified Date:	21-MAY-2009	Status:	MODERATED

I personally have seen these errors occur after an RMAN duplicate command, the show up in the duplicated database.

 

Novice DBA Twitter Twibe

Are you a new Oracle DBA totally overwhelmed? Let me know about some of your concerns, problems or issues that you deal with.  Join the twitter twibe at http://www.twibes.com/novicedba 

  Search http://twitter.com for #novicedba to see all of the latest tweets

 

My Backup DBA

Are you the only Oracle DBA at your company?  How do you provide Disaster Recovery and Redundancy for personnel in that situation?

Metalink (now My Oracle Support) has a tool that provides an “Automatic Document Repository” called Oracle Configuration Manager (OCM).  I mean automatic in the sense that if you are out of the office, temporarily or permanently,  there are documented configurations of the systems for your CSI number.   The repository is My Oracle Support, Oracle’s support site.  The configurations are any that you enabled the OCM client for.  They are of your choosing whether you only include production and/or nonproduction systems.

What information is there? Hardware details, software installs (not just Oracle products), databases, Oracle application servers, Oracle Business Suite details.  Enough information to recreate your site if there is a complete disaster.   The GUI interface allows managers and other IT personnel to see how the nodes, applications are related and fit into your architectural framework.  The information can only updated by the upload process keeping the information unadulterated.

Each node with an installed agent can be automated to upload any changes on a daily basis or interval of your choosing.  If the node doesn’t have direct access to the Internet, a standalone intermediary box can house the repeater which then would upload to My Oracle Support website.   OCM  is now an optional part of  any of the 10.2.0.4+ Oracle GUI install.  OCM Collector is found by logging into My Oracle Support and selecting the collector tab.

Mass Deployment Utility

 Installing the agent has been vastly improved with a mass deployment utility.  On My Oracle Support collector tab find “Configuration Manager Repeater & Mass Deployment Tools” and the OCM Companion Distribution Guide.    The template file is csv format which you may find dificult to edit using vi or vim…since there wasn’t an initial entry.  Once I had the first entry done (required columns vs. optional) editing with a command line tool became easier.  Security using this utility is built in and configurable.  You can enter a password at the prompt, or allow the password utility to encrypt the open text in the template file. 

Running the utility was fast from a single node that had SSH access to all entries in the template.  It autodetects if OCM was already installed and bypasses those entries. 

The only single thing you may have trouble with is the required JAVA version is higher than what a 10.2.0.x database would ordinarily have installed.  Other prerequisities include SSH on Linux or CYGWIN for Windows.

The only real downside is that all configuration information is available to everyone with the same CSI number.  In a small shop that isn’t a problem as long as Metalink access is maintained properly when personnel changes.  Providing granular group access to My Oracle Support Configurations would be a welcome enhancement.

 

Troubleshooting Oracle Wallet – Hari Kari style

Recently we had the pleasure of a last minute emergency replacement of a SSL certificate.  I think most IT shops have been there, done that.  But the problem is that the replacement renewal certificate didn’t work – now how does one proceed? 

1.  Something is wrong after replacing SSL certificate on the hardware load balancer.  Symptoms – Page not found errors, ORA-29024: Certificate validation failure in the Oracle Application Server logs.  Revert to older certificate all is well again.  But no time to waste since it expires tomorrow at 18:59 MST.  Yeeegads! 

2. This is an external CAS ticket server authenticating to an Oracle Database using UTL_HTTP calls.  Doing the following select statement as a user with execute privileges on UTL_HTTP also shows the same issue, no matter the URL - 

select utl_http.request ('https://hostname.domain/cas/login'
,null,null,null) from dual;
ERROR at line 1: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577 
ORA-29024: Certificate validation failure  
ORA-06512: at line 1

3. Ok, this is Oracle so I know the Oracle Wallet is involved.  It resides on the database server since we apply certificates on the load balancer, your situation may involve an Oracle Wallet on OAS as well.   The following select statement checks if the wallet is valid, not corrupt and has the correct password.

select utl_http.request ('https://www.verisign.com/',null,
'file: etc/ORACLE/WALLETS/oracle','password') from dual; 
ERROR at line 1: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29024: Certificate validation failure ORA-06512: at line 1

4.  Yeegads again!  There is something wrong with the wallet, now how can that be?  DBA didn’t change anything, how does replacing a certificate invalidate the wallet? So this is where the Hari Kari starts – I backup the old wallet directory, create a new one wallet in the same location, same password.  Still doesn’t work, same error. 

Now the document on ‘My Oracle Support”  -

Troubleshooting ORA-29024:Certificate Validation Failure
Doc ID: 756978.1

gets me started but I can’t find anything wrong with the wallet.  I use both orapki (wallet command line utility) and OWM (gui).  There are no problems opening the wallet viewing the chain, etc.

> orapki wallet display -wallet .
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=GTE CyberTrust Root,O=GTE Corporation,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject: CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net
Subject: CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net
Subject: CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US

5. The knife goes deeper into the flesh, I am really hurting now.  It is 10:00 pm been on the cell phone with other IT personnel involved over 60 minutes (they are supposed to be in my “circle of friends” so hopefully the cell phone bill won’t kill me either).  Continue on with the troubleshooting document –  ”Both the user and trusted certificates are valid and not expired or revoked” – I don’t have any user certificates…the others seem fine. I have recreated the wallet several times, bounced the database/load balancer/OAS/CAS server each time (can you sense the ”desperation”) because I find in another document  

 How To Replace An Expired Or Expiring Certificate in Wallet Manager
Doc ID: 303299.1 

(why wasn’t this step in the troubleshooting ORA-29024 document?)  that  you have to “Restart the component that uses the Wallet i.e Webcache, HTTP Server, or Database, as the Wallet is stored in memory and will not be re-read until the component is restarted” .  After several hours of testing at least at this point I have determined that the wallet is basically valid because it worked yesterday but not today with the new certificate.  But Why?

6.  Last step in the document - 

"If this error is seen while using with UTL_HTTPS set up than , check :
---> Whether all the certificates of the secure website are there
in the wallet and the certificate chain is complete." 

Well that may be the issue, checking back with the network guy who ordered the replacement cert, he seemed to think the certificate was slightly different than last time.  We had double-checked it’s validity by viewing it with several browsers.  At this point everyone went home, I entered an Oracle SR (since the database wasn’t down I knew there wouldn’t be a quick response), programmer guy emailed his CAS colleagues, network guy entered an emergency support request through Verisign.

7.  The next morning I started check ing Verisign’s website, programmer guy mentioned he thought it might be a chaining issue…possibly a new or updated intermediate certificate was needed.  The knife was still in deep which had made it hard to sleep.   Next morning network guy says definitely a new intermediate certificate was needed according to Verisign…checking Verisign’s website there are several.  You will need your certificate order number to get the right one.  Verisign support had attached it in the support request.

https://knowledge.verisign.com/support/ssl-certificates-support/index?page=content&id=AR657&actp=LIST  (Verisign Intermediate CA Certificates)

8.  How to install this intermediate certificate?  Easiest way is to use the orapki utility. Previous hari kari work with intermediate certificates gave me the knowledge that Oracle was “picky” with certificates and that the intermediate certificate needed to be named ca.crt to work.  I renamed the file (transferred in binary format) to ca.crt put it in the /etc/ORACLE/WALLETS/oracle location.  But we aren’t finished yet.  It won’t import, it just gives me an error unable to open wallet .  I give it the full path, still same error. 

Yeegads! OOmph! Ok…I had just imported the intermediate certificate into a non-production instance and got it working.  Why is production giving me grief? 

9.  I backed up (moved) the old wallet, more slice and dice.  There are several directories with old wallets…none of them worked for the new certificate during the previous night’s testing but they all worked with the old certificate but none of them would let me import the intermediate certificate.  AAgh!  Created a new wallet, I used a different command-line utility (Reflection) instead of Putty because in another document from My Oracle Support it mentioned that the keyboard might not be typing the wallet password correctly. What! I have used this console utility forever, first I have heard of this.  So I create the new wallet using Reflection and put a single quote around the password.  So take that! and that!  Back you evildoer! Back away! This is all out war!  Still problems.

cd /etc/ORACLE/WALLETS/oracle
orapki wallet create -wallet . -auto_login -pwd 'password'
orapki wallet add -wallet . -trusted_cert -cert ca.crt -pwd 'password'
(don't expect anything to tell you this was successful you are just looking
 to see if any errors occur)
select utl_http.request ('https://www.verisign.com/',null, 
'file:/etc/ORACLE/WALLETS/oracle','password') from dual;
select utl_http.request ('https://www.verisign.com/',null,
*ERROR at line 1:ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-28759: failure to open file ORA-06512: at line 1

10. At least the ERROR MESSAGE changed!  Sorry but at this point my desperation was running full tilt, anything makes me giddy. I restared the production database, I knew that was probably going to happen.  I discreetly change the status of my IM as busy to reduce the numbers of attacking messages.  They start fast and furious. 

select utl_http.request ('https://domainname/cas/login',null,
'file:/etc/ORACLE/WALLETS/oracle,'password') from dual;
select utl_http.request ('https://domainname/cas/login',
null,'file:/etc/ORACLE/WALLETS/oracle','password') from dual
* ERROR at line 1: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29106: Cannot import PKCS #12 wallet. ORA-06512: at line 1

11. I realize that I typed the password incorrectly in the SQL statement.  Retyped it correctly and it returns a valid web page, it is finally over. I remove the knife, it might heal with some time off.

 

Oracle Application Server 10.1.3.1 – How to manually add PLSQL DAD functionality

Updated Note:  Thanks for Brandon Cole from ISU that pointed out that it is called a JDBC OCI Data Source Connection instead of a DAD.  I haven’t tested this yet, see the Metalink Document referenced below.

Subject: How to Create a JDBC OCI Data Source Connection For OAS 10.1.3.x

Doc ID: 728235.1 Type: HOWTO

Modified Date: 10-NOV-2008 Status: PUBLISHED

 

 

 

Yuck, Yuck, Yuck…Sorry to start off so glum but this felt like when I go out in the garden and get that heavy clay stuff on my shoes.  I walk back in the house with my sneakers with their lovely ridged bottoms caked full of mud.  Tromp, Tromp, Tromp – now there are little mud pieces all over the house.  This is what it feels like messing with OAS 10.1.3.1 standalone server. 

1st piece of mud hits the floor, PLOP!   Install 10.1.3.1, patched to 10.1.3.4

Second piece of mud hits the floor PLOP! PLOP! - Where is the Application Server Control?  Look through the docs it mentions it should be http://nodename:defaultport/em  Look at the $ORACLE_HOME/install/readme.txt that flashed up during the patch install mentions the following-

“Application Server Control Console is not running in this instance of Oracle Application Server. You can manage this instance remotely through another instance of Oracle Application Server that is configured to run Application Server Control Console.” 

What does that mean?

Third piece of mud hits the floor PLOP! PLOP! PLOP!    The error when accessing the em page is 404 file not found.   So now I am confused, is it broken or just not supposed to be there to start with?  Ok, I start looking at the other instance (10.1.2.3) of OAS control on this same node.  No way to add another instance to a cluster or farm or whatever because it is a standalone version as well.   I head to Metalink, try using the newfangled version (you know which one that is, and I was on the beta feedback team for this) because there is a possibility it is broken. 

YUP! It is broken after the patchset.  Subject: OracleAS 10.1.3 AS Control Returns ‘404 Not Found’ After Applying Patchset 2 or Higher 427562.1  Time for a small victory dance (I don’t move my shoes at this point).  

But wait folks!  There is more to this.  I have forgotten the password which was set at install time for the OC4Jadmin password on the OAS control page.   PLOP!PLOP!PLOP!PLOP!   On to metalink again! Subject: Unable To Login To The Esb Console or Application Server Control  Doc ID: 470680.1 and  Subject: How to change the OC4Jadmin Password
Doc ID: 576587.1        It is fitting that on one of the Metalink docs the final step reminds you how naughty you were to forget that password.  This is a direct quote from the doc- “5. Your new password will now be whatever was entered after the exclamation point. Notice if you open the file again, the value is again encrypted. It is important to remember the password for further deploy or undeploy actions.”   Also a little side not to this…it is normal for it to ask you again to reenter the password, it states there is an error but that seems to be normal according to Doc 4700680.1.

Ok…now what was I supposed to be doing when all of this started, oh yes! I remember now configure a plsql dad.   PLOP! PLOP! PLOP! PLOP! PLOP!  Looking at the Application Server Console – there is nothing configurable with HTTP, PLSQL, etc.  It is all about OC4J containers.  

Now what?  Back in the dredges of my brain…OAS 9i comes back to visit me.  Remember April when you did everything manually…editing dads.conf and httpd.conf to your hearts content without opmn getting in the way?  “I can do this, this is a Unix system!” Direct quote from Speilberg’s movie Jurassic Park.  The scene where the young blonde grandchild sat down to the console and got everything going again.  Let me know if I didn’t get the quote right.  (time for a victory dance, OhNO! not yet…no real work has been accomplished and there is still mud on my shoes).

Stopped everything.  $ORACLE_HOME/opmn/bin/opmnctl stopall    Copied over a working dads.conf from another box.  Edited httpd.conf to add a virtual server to listen on another port for this dad and accompanying files location – nothing fancy…this is just to prove it can be done on this OAS instance.  This will be edited later and made more secure with SSL, right now it is secure behind a firewall.  (oh boy! I feel another post coming on).

Listen portno

<VirtualHost nodename:portno>
ServerName fullyqualifiedservername
DirectoryIndex page.htm
DocumentRoot “/documents”
</VirtualHost>

$ORACLE_HOME/opmn/bin/opmnctl startall   Ok….PLOP! PLOP! PLOP! PLOP! PLOP! PLOP! Won’t start and it complains something about “invalid form factor” in the $ORACLE_HOME/opmn/logs/HTTP_Server~1.log .  Back to Metalink (maybe they should start charging by the number of time you search for the same document over and over, similar to the fees they want to charge people who drive a lot…you know what I am talking about.  A heavy users fee for the roads in highly congested areas, maybe there should be something similar for the Internet Highway!) 

Duh! I somehow ran $ORACLE_HOME/opmn/bin/opmnctl startall   as root.  I’m bad….remembering how naughty I was before when I forgot the password.  Well I follow the instructions on Metalink again!  ERRORS STARTING PROCESSES THROUGH OPMN “RCV: Permission denied”, “Communication error with the OPMN server local port”  Doc ID: 390641.1

 Ok!  Restarted the services correctly this time…All is well.  “I can do this”. 

By the way there is a Document on Metalink (where else would I send you to?) for How To Create a DAD using OHS Standalone or From Oracle Database 10g Companion CD
Doc ID: 295535.1  It will give you all of the details that I left out. 

 I am off to the back porch because there is still mud on my shoes!

 

Best Practices Booklet – Third Edition Corrections

Wrote an article titled

“Migrating to 11g – Step-Ordered Approach”

 in the IOUG’s Best Practices Booklet distributed with the SELECT Journal 3rd Quarter 2008 and found a typo after it was published.

On page 4 in Listing 2:  (this is the corrected entry)

ORACLE_HOME=/u01/app/oracle/product/10gR2

PATH=$ORACLE_HOME/bin:$PATH

ORACLE_SID=SID

# New Recommendation for 11g Diagnostics

ORACLE_BASE=/u01/app/oracle

TNS_ADMIN=/u01/app/oracle/product/11g/network/admin

export ORACLE_SID ORACLE_HOME PATH ORACLE_BASE TNS_ADMIN

 

Migrating to AL32UTF8 on 10gR2 for BANNER

Updated:

SUU

is now live on

Banner 8.x….by using the following procedure.

 

Updated Information: 

Subject: Data Pump Export Does Not Export Disabled Primary Key Constraints   

Doc ID: Note:455311.1. Enable the PK constraints manually or with a dynamic SQL script before run Data Pump Export. Then disable the PK constraints manually or with a dynamic SQL script in both source and destination database…or use EXP/IMP.  Currently not fixed until 11.2 listed as Bug 5523375. 

This didn’t seem to be a problem for my environment… no Primary Key disabled constraints except for system owned LOGMNR objects.

 

See…

Character Set Consolidation for the Oracle Database Doc ID: Note:306411.1

Comments on some popular database character sets that are not recommended

• US7ASCII: better to migrate to WE8MSWIN1252, or WE8ISO8859P15 etc.
• WE8ISO8859P1: WE8MSWIN1252 is a superset
• UTF8: better to migrate to AL32UTF8
• ZHS16CGB231280: ZHS16GBK is a superset
• ZHS32GB18030: better to migrate to AL32UTF8
• KO16KSC5601: KO16MSWIN949 is a superset
• ZHT16BIG5: ZHT16MSWIN950 solves various problems of ZHT16BIG5

If you are a SunGardHE Banner institution then the TEST CASE posted here especially applies to your situation since they are requiring AL32UTF8 for Banner 8.   Thanks to S.Harden and C.Aabbott  and many others of the BORACLE list who provided a lot of the advance beta work on this information.  This is not a rehash of their work with 10gR2  but a log of the steps for 10gR2 to work with Banner 8 with complete information from beginning to end for a smaller database.

This test case will move to UTF8 characterset using the expdp/impdp process.  You could do a manual csalter method but the amount of time and effort required would not be worth it.  The SOURCE database is running Oracle Enterprise Edition 10.2.0.4 and approximately ~50GB in size.  Export/Import could also be used but requires several additional steps of importing the structures with no data, converting all char/varchar2 columns from BYTE to CHAR, then importing data. See Metalink Note:313175.1 for the script to convert the columns.

Why two migrations?  In this case there was Lossy Data that couldn’t be converted from (WE8ISO8859P1 to AL32UTF8) but could be eliminated by converting to WE8MSWIN1252 (which makes sense because it is the superset of WE8ISO8859P1) as an intermediate step.  How do I know which characterset is installed?  Note: The actual migration path depends on your starting characterset.

SQL>  select nls_characterset from nls_database_parameters;

See Metalink Note:260192.1

Changing WE8ISO8859P1/ WE8ISO8859P15 or WE8MSWIN1252 to (AL32)UTF8

1.   Install the CSSCAN tool in the SOURCE database.

SQL> connect / as sysdba

@?/rdbms/admin/csminst.sql (change default tablespace by editing script beforehand)

2.  Run CSSCAN on the SOURCE database.

csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=WE8ISO8859P1 LOG=WE8_TO_WE8 CAPTURE=Y ARRAY=1000000

Duration:Time Started : 2008-06-17 14:54:30

Time Completed: 2008-06-17 15:06:51

Application Data Conversion Summary

Datatype      Changeless       Convertible     Truncation      Lossy

VARCHAR2     1,842,002,371             0                     0           438

CHAR                   41,194,938             0                    0               0

LONG                       427,805             0                     0                1

CLOB                     6,584,926             0                     0            133

VARRAY                               0            0                      0               0

__________________________________________________________________

To make this entire project feasible in a weekend, the BANNER DBA needs to have done several prepatory steps before starting the final migration in production.

• Created a new code tree for BANNER 8.
• Installed the dependent ICU (International Components for Unicode) see SunGardHE FAQ’s
• Finished all C++ and COBOL compiles successfully for all modules in that new code tree.
• Finished all Forms/Reports compiles in a 10.1.2.2 or 10.1.2.3 Oracle Application Server.
• Done this entire process on a clone of production SEVERAL times.
• Made sure end user testing was adequately done.
• Decided on which features in 10gR2 will be used now vs migrated to later.
• TEST by running batch jobs that produce LARGE amount of archive logs to gauge performance.
• Security changes in BANNER 8 need to be researched and tested.
• Resolve all TRUNCATED data.

3. Run csscan again on SOURCE with different parameters to prepare to convert to WE8MSWIN1252 character set since there is LOSSY data.

At this point it would be advisable to mount the SOURCE in RESTRICTED MODE to prevent any updates from clients. STOP all batch processing, STOP GURJOBS, PIPES, POSTING.  STOP the SOURCE database LISTENER.

 csscan FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=WE8MSWIN1252 LOG=WE8WIN_to_WE8WIN CAPTURE=Y ARRAY=1000000

 Find the WE8WIN_to_WE8WIN.txt file and if you find the following entries then the lossy data will be taken care of by migrating to an intermediate (actually a super) character set.

 All character type data in the data dictionary remain the same in the new character set

All character type application data remain the same in the new character set

The data dictionary can be safely migrated using the CSALTER script

4. Change the SOURCE database characterset to WE8MSWIN1252. Do you have a good backup at this point?

 Important: Do not allow any changes to the database between the previous csscan and the characterset change.

Connect as sysdba set PARALLEL_SERVER and CLUSTER_DATABASE parameters to FALSE; job_queue_processes and aq_tm_processes are set to zero.

Connect as sysdba

SQL > shutdown immediate

SQL > startup restrict

SPOOL CSALTER.log

SQL > @?/rdbms/admin/csalter.plb (respond with a Y)

SQL > shutdown

SQL > startup

SQL > select * from v$NLS_PARAMETERS;

 

 5. Create the new database (TARGET) with AL32UTF8 characterset

Be sure and apply patchset for BUG 5874989. Set semantics to CHAR.

SQL > alter system set NLS_LENGTH_SEMANTICS=CHAR scope=both;

Change init.ora parameters of the TARGET database to speed up the IMPDP process.

DB_BLOCK_CHECKING=FALSE

DB_BLOCK_CHECKSUM=FALSE

Other init.ora parameters such as PGA_AGGREGATE_TARGET, SORT_AREA_SIZE, SHARED_POOL_SIZE, SGA_MAX_SIZE and PARALLEL_MAX_SERVERS can be adjusted but will vary depending on your environment.

Precreate your BANNER tablespaces if you want to recreate or change the datafile locations. 

Autoextend USERS, TEMP, UNDO and any BANNER tablespaces to UNLIMITED.  This will prevent the job from stopping due to lack of storage.  Be sure and reset them back after you are finished.

6.  Run csscan to prepare to converto to AL32UTF8 character set.

You won’t be using the CSALTER method to do the conversion but this step will identify any data that is labeled TRUNCATION. 

 csscan FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=AL32UTF8 LOG= WE8TOUTF8 CAPTURE=Y ARRAY=1000000

Changing the NLS_LENGTH_SEMANTICS to CHAR (later in this post) will fix the truncation data.  

List of Tables/Columns that contained TRUNCATION data in this case (23 rows in total), yours will be different.

• FIMSMGR.FOBTEXT
• SATURN.SARQUAN
• SATURN.SARRQST
• SATURN.SPRCMNT

 7. Make a full DATAPUMP export of the SOURCE database.

Make sure NLS_LANG is set correctly, doublecheck it.

Unix example: export NLS_LANG=’AMERICAN_AMERICA.WE8MSWIN1252′

expdp userid/password full=y dumpfile=expSOURCE.dmp

Spool any direct grants which won’t be migrated to the TARGET database during the IMPDP process.  Review the script, it may be advisable to add the list of grantees to be excluded such as the default roles (DBA, EXP_FULL_DATABASE, etc). 

 set head off

set pagesize 0

set feedback off

spool grants_from_sys.sql

select ‘grant ‘||privilege||’ on ‘||owner||’.'||

table_name||’ to ‘||grantee||’ ‘||

decode(grantable,’YES’,'WITH Grant option’)||’;’

from dba_tab_privs

where owner = ‘SYS’

/

select ‘grant ‘||privilege||’ (‘||column_name||’) ‘||

‘ on ‘||owner||’.'||table_name||’ to ‘||grantee||’ ‘||

decode(grantable,’YES’,'WITH Grant option’)||’;’

from dba_col_privs

where owner = ‘SYS’

/  

 

8. Use IMPDP on the TARGET database to import everything at once, not a problem for this ~50GB database. 

BEFORE you start the IMPDP make sure NLS_LENGTH_SEMANTICS are set correctly on the TARGET database. Do NOT EXECUTE the following test as SYS…NLS_LENGTH_SEMANTICS won’t affect anything owned by SYS.

alter system set nls_length_semantics=’CHAR’ scope=both;
     create table test(v1 varchar2(10));
     select CHAR_USED from dba_tab_columns where table_name=’TEST’;

you would expect 10 character (C) for CHAR semantics , but if it is 10 BYTE (B) then you need to restart the database and start a new session. See the following Metalink note for other issues when setting NLS_LENGTH_SEMANTICS.

 

Examples and limits of BYTE and CHAR semantics usage      

        

Estimated Time for ~50GB is 2.5 hours, your mileage may vary depending on hardware and operating system.

This migration was done in RH AS 4 on Dell Commodity Hardware with 64-bit Oracle Enterprise RDBMS.

 Again make sure the NLS_LANG is set to your SOURCE characterset, this is what makes the conversion happen during the IMPDP process. 

Unix example: export NLS_LANG=’AMERICAN_AMERICA.WE8MSWIN1252′.  export NLS_LANG=’AMERICAN_AMERICA.WE8MSWIN1252′create directory temp_pump as ‘/backup/export/SID/’;

contents of example parfile, impdpfull.par FULL=Y DUMPFILE=expdpSID.dmp DIRECTORY=TEMP_PUMP LOGFILE=impdp_data.log PARALLEL=6

nohup impdp system/password parfile=imdpfull.par &

You could move objects to new tablespaces by splitting up the process into more than one part. At this point the choice was made to precreate the tablespaces in a different location on the TARGET database than the SOURCE database, the default parameter for REUSE_DATAFILES=N for the IMPDP so it won’t overwrite existing datafiles.  See IMPDP Errors later in the post to resolve specific issues related to this step.

9.  Restore TARGET init.ora parameters appropriate for production.

 Run the spooled grants_from_sys.sql.  Run Oracle’s utility NID to rename the database to what the SOURCE production had before, if desired.  Turn off autoextend for all the datafiles or at least give them a maxsize.

10.  Change the BANNER home

…..to the one that you have been testing against with everything already precompiled (remember to do all Forms, C and COBOL). This will save LOADS of time.  There is a script provided that is OS specific see $BANNER_HOME/module/misc

Items that need to be edited (they were redelivered and will overwrite your current copies):

• $BANNER_HOME/admin/banenv
• $BANNER_HOME/general/c/sctproc.mk (find the templates from SunGard or another school with the same OS) 
• $BANNER_HOME/general/cob/sctprocb.mk
• $BANNER_HOME/general/misc/gjajobs.shl

SunGardHE suggests putting the new environmental variables for UTF8 in the .profile for the BANNER account that compiles C and COBOL.  Since the variables are database specific, it really should only be executed at certain times and not globally.  A good location is to have it execute when you source the Oracle-supplied /usr/local/bin/oraenv file under custom code for the UTF8 database, see the following example snippet.

if [ "$ORACLE_SID" = "SID" ] ; then
. /u01/app/sct/SID.ini
. /u01/app/sct/banutf8codetree/admin/banenv
fi

Contents of SID.ini file (we run gurjobs on a separate host so we set the TWO_TASK variable)

TWO_TASK=SID
export TWO_TASK
ORACLE_HOME=/u03/10gR2client
export ORACLE_HOME
TNS_ADMIN=/u03/10gR2client/network/admin
export TNS_ADMIN

NOTE:  I removed the follwing NLS_LANG settings from our environment on the jobsub box….there was some debate on whether it was needed.  It makes SQLPLUS output larger than in Banner 7.x which would affect reports and other types of spooled output.

NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_LANG

#ICU additions
#———————–
LANG=en_US.utf8
LC_ALL=en_US.utf8
LC_COLLATE=en_US.utf8
LC_CTYPE=en_US.utf8
LC_MESSAGES=en_US.utf8
LC_MONETARY=en_US.utf8
LC_NUMERIC=en_US.utf8
LC_TIME=en_US.utf8
export LANG LC_ALL LC_COLLATE LC_CTYPE LC_MESSAGES LC_MONETARY LC_NUMERIC LC_TIME
#—————————— 

 

 

11.  Install BANNER 8 upgrades/patches appropriate for your environment. 

To speed things up: use the AUTOMATED installer, skip all of the GURULTRP.sql steps until the end.  The order I used for the install the major releases:

• GENERAL 8.0
• ADVANCEMENT 8.0
• POSNCTL 8.0
• HUMAN RESOURCES 8.0
• ACCOUNTS RECEIVABLE 8.0
• FINANCE 8.0
• STUDENT 8.0
• INTEGRATION COMPONENTS 8.0
• FINANCIAL AID 8.0
• FINANCIAL AID 8.1
• WEBTAILOR 8.0
• WEB GENERAL 8.0
• WEB ADVANCEMENT 8.0
• WEB HUMAN RESOURCES 8.0
• WEB FINANCE 8.0
• WEB STUDENT 8.0
• WEB FINAID 8.1
• And this list repeats in the same order for all of the 8.1  and 8.2 upgrades.
• plus patches

 Hopefully you already finished all of the compiles (C, Cobol, Forms) ahead of time.

Here is a script to do a mass compile of all of the forms at once….on a Unix box.  I only do this when no end users are online as it degrades performance, there is no problem compiling one script at a time like aluform.shl

#do_all_forms.shl

sh ./aluform.shl >aluform.log 2>&1 &

sh ./fimform.shl >fimform.log 2>&1 &

sh ./genform.shl >genform.log 2>&1 &

sh ./comform.shl >comform.log 2>&1 &

sh ./payform.shl >payform.log 2>&1 &

sh ./posform.shl >posform.log 2>&1 &

sh ./resform.shl >resform.log 2>&1 &

sh ./stuform.shl >stuform.log 2>&1 &

sh ./tasform.shl >tasform.log 2>&1 &

 

12.  Restart the listener, gurjobs, sleepwake, pipes, workflow etc.  

Change the production location of the forms on the Oracle Application Server to the precompiled Banner 8 versions.

Speific issues or Errors Related to IMPDP :

Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 342 error(s) at 14:44:32

1.  ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type TABLE:”BANINST1″.”AQ$_EVENT_BULKSYNC_TABLE_H” creation failed

FAQ #1-321736  - How can I rebuild BANAQ objects (gnewquesub.sql) or fix invalid objects AQ$EVENT_BULKSYNC_TABLE AQ$EVENT_SYNC_TABLE 

2.  ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type TABLE:”BANINST1″.”CREATE$JAVA$LOB$TABLE” creation failed

 

Are you an Oracle DBA Dinosaur?

Recent discussions with colleagues about the new 11g Diagnosability Features, someone came up with the term “DBA Dinosaurs”.

How would you know if you are one?

1.  Are you still on version 7.3.4 of Oracle?  

2.  Do you stay in your office, cubicle, work area?  Rarely go to meetings with other human beings? 

3.  Do people wonder what do you do all day?

4.  When was the last time you attended an oracle conference?  If you attended did you “network” with others at the conference?  Did you bring any business cards to pass around?

5.  Do you only “linger” on technical email discussion lists?

6.  Do you know what the newest version of Oracle is?   Can you list at least 5 of the new features for that version?

7.  Do you have a migration to the next CPU, patchset, version, hardware replacement in progress? 

Number 7 to me is the one that says it all…..change is inevitable in our business.  Are you changing with it?

If you aren’t moving then you are stagnating, soon to be extinct.  Think about it.