Oracle DBA tips for CNC

Sure, you might know it all about databases, but this is not for you.  This is for me, when I forget all of these commands next week...  I've got somewhere to go!

Stay tuned for some banter about CHAR to NCHAR, it's my next port of call!

List your table spaces and space remaining:

SELECT
df.tablespace_name AS "Tablespace",
df.bytes / (1024 * 1024 * 1024) AS "Size (GB)",
Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"    
FROM
(
SELECT
tablespace_name,
Sum(bytes) AS bytes
FROM
dba_free_space
GROUP BY
tablespace_name
) fs,
(
SELECT
tablespace_name,
SUM(bytes) AS bytes
FROM
dba_data_files
GROUP BY
tablespace_name
) df
WHERE
fs.tablespace_name = df.tablespace_name
ORDER BY 3 desc


Tablespace                     Size (GB)              Free (GB)              
------------------------------ ---------------------- ---------------------- 
PRODDTAT                       12.05078125            7                      
UNDOTBS1                       3.7109375              3                      
TESTDTAT                       21.953125              2                      
TESTDTAI                       20.953125              1                      
PRODDTAI                       1.953125               1                      
SYS7333T                       0.029296875            0                    

List your datafiles and amount used:

Now, list your database files and how much of them are being used, whether they are autoextend


SELECT substr(df.file_name,1,45),
df.tablespace_name,
df. status,
(df.bytes/1024000) t,
round((fs.s/df.bytes*100),2) p,
decode (ae.y,1,'YES','NO') a
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
--AND df.tablespace_name in ('TESTDTAT', 'TESTDTAI')
ORDER BY t desc;


/u02/jdedwardsppack/ORCL/Tables/proddtat02.db PRODDTAT AVAILABLE 10485.76 60.23 NO
/u02/jdedwardsppack/ORCL/Tables/testdtat03.db TESTDTAT AVAILABLE 10485.76 2.11 NO
/u02/jdedwardsppack/ORCL/Tables/testdtat05.db TESTDTAT AVAILABLE 5242.88 41.99 NO
/u02/oracle/oradata/jdepoc/undotbs01.dbf UNDOTBS1 AVAILABLE 3891.2 91 YES
/u02/jdedwardsppack/ORCL/Tables/dv910t02.dbf DV910T AVAILABLE 3686.4 16.14 YES
/u02/jdedwardsppack/ORCL/Tables/testdtai05.db TESTDTAI AVAILABLE 2097.152 70.75 NO

If you are running out of space, add some more:

ALTER TABLESPACE testdtat ADD DATAFILE '/u02/jdedwardsppack/ORCL/Tables/testdtat04.dbf' SIZE 5G ;

Check that the physical file system can extend of course (df -k /u02)

If you need some space back, you could try a:

alter database datafile '/u02/jdedwardsppack/ORCL/Tables/proddtat03.dbf' RESIZE 100M ;

Note that all of the above is pretty hard.  If you have command line on the DB server, check if emctl is running:

>emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://aubdc00-jdb01t:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/112/aubdc00-jdb01t_jdepoc/sysman/log

The above gives you the URL to go to for the dbconsole.  This is a great graphical way of doing everything easily.  You can chuck the above scripts in the bin and click all your problems away!  (of course, unless you are like me where the console will not start :-(

Refresh your TEST environment with DATAPUMP JDE

Please do not use R98403, if you are - you might need to give up on CNC, or at least read on.  If you are on a 400, data refresh is easy (cept for that damn auditing!!!).  If you are oracle use data pump, if you are SQL, back / restore and sp_changeObjectOwner!

Here is the data pump instructions:

create directory myDpump as '/u02/oracle/dpump' ;

grant READ,WRITE ON DIRECTORY myDpump TO JDE_ROLE ;

then execute the datapump for an export and then an import.

I tend to use parfiles, so I can move them from site to site.

expdp PRODDTA/PRODDTA@JDEPOC parfile=./prod.par


# PD expdp parfile
schemas=PRODCTL,PRODDTA
#content=data_only
content=all
#directory=data_pump_dir 
directory=data_pump_myriad_dir 
dumpfile=prod.dmp
logfile=prod_expdp.log   
job_name=prod_expdp


impdp TESTDTA/TESTDTA@JDEPOC parfile=./prod.par


# DV impdp parfile
schemas=PRODCTL,PRODDTA
remap_schema=PRODCTL:TESTCTL,PRODDTA:TESTDTA
remap_tablespace=PRODCTLI:TESTCTLI,PRODCTLT:TESTCTLT,PRODDTAI:TESTDTAI,PRODDTAT:TESTDTAT
#table_exists_action=truncate
table_exists_action=replace
#content=data_only
content=all
#directory=data_pump_dir 
directory=data_pump_myriad_dir 
dumpfile=prod.dmp 
logfile=test_impdp.log
job_name=test_impdp
#parallel=4

Thanks to Craig VDV for giving me a start on this one!  

If you are enterprise edition of the DB, bang in a parallel=4 at the bottom - this will kick of 4 threads to do your work.

DB link from Oracle to AS/400 - platform migration integrities - AS/400 to oracle

I must say thanks to SVDS (any CNC in Aus will know who this is).  He showed me this wonderful creation and I'm in debt to him for it.

Now, here is something that has saved me a lot of time and a lot of heart ache!  I've used a db link between oracle and AS/400.  This has been invaluable for running integrities between my upgrade / platform migration.  

Essentially you create a DB link on the oracle side and then use the @DBLINK sytax at the end of the table when referring to AS/400 based tables - awesome!

To see information about links, use SQL like:


COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS

http://docs.oracle.com/cd/B19306_01/gateways.102/b16222/using.htm#i20148 is a good place to start

Create the link

CREATE DATABASE LINK myAS400
        CONNECT TO ONEWORLD IDENTIFIED BY NUMPTY99
        USING 'SHANNON.WORLD';

Where your tns_name_entry is defined something like:


SHANNON.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = xx.77.66.44)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = ORAGATE)
    )
    (HS = OK)
  )

What I hear you say, surely it cannot be that simple...  And yet it is.  For simple SQL this is a blast.  Here are some examples:

See if all the custom objects made it over from XE:

select t1.siobnm, t1.sisy, t2.sistce from ol7333.f9860@myAS400 t1, ol7333.f9861@cms t2 

where t1.siobnm = t2.siobnm and t2.sipathcd = 'DV7333'

and t1.siobnm in (select siobnm from ol7333.f9860@myAS400 t3 where not exists

(select 1 from ol910.f9860 t4

where t3.siobnm = t4.siobnm) 

and t3.siobnm like '%55%');

Data count reconciliation between XE and JDE91 with platform migration from AS/400 to Oracle

create table testdta.rowcounts (table_name varchar (20), AS400_count integer, oracle_count integer) ;

/*  Add your table names from 9.1 data */

insert into testdta.rowcounts (select table_name, 0, 0 from all_Tables where owner = 'PRODDTA') ;

commit ;

/*  Add any tables from the 400 that may not already be in the reconciliation table */

insert into testdta.rowcounts (

select table_name, 0, 0

from qsys2.systables@myAS400  t1

where table_schema = 'TESTDTA'

and not exists (select 1 

  FROM all_tables

  WHERE owner = 'PRODDTA'

  AND table_name = t1.table_name)) ;

/*  update the AS/400 row count data  - you need to run the results of this statement*/

select 'UPDATE TESTDTA.ROWCOUNTS set AS400_count = ( SELECT COUNT(1) FROM ' || 'TESTDTA.' || table_name || '@CMS ' || ') WHERE table_name = ''' || table_name || ''';' 

from qsys2.systables@CMS t1

where table_schema = 'TESTDTA'

/*update the oracle row counts - you need to run the results of this statement*/

select distinct 'UPDATE TESTDTA.ROWCOUNTS set oracle_count = ( SELECT COUNT(1) FROM ' || 'PRODDTA.' || table_name || ' ' || ') WHERE table_name = ''' || table_name || ''';' 

from all_tables t1

where owner = 'PRODDTA'

commit ;

/*  here is your data, NICE! */

select * from TESTDTA.rowcounts where (as400_count <>0 or oracle_count <> 0);

Refresh TASKS from PS910

I was missing fast path and other options on my fat client after an upgrade.  It was quite annoying.  I ran the following scripts to insert all PS records that did not exist:


insert into prodctl.f9000 t1
(select * from ps910ctl.f9000 t2
where not exists (select 1 from prodctl.f9000 t3
where t3.tmtaskid = t2.tmtaskid or t3.tmtasknm = t2.tmtasknm));
commit ;


insert into prodctl.f9001 t1
(select * from ps910ctl.f9001 t2
where not exists (select 1 from prodctl.f9001 t3
where t3.TRRLTYPE = t2.TRRLTYPE
and t3.TRFRMREL =t2.TRFRMREL
and t3.TRTHRREL =t2.TRTHRREL
and t3.TRPARNTTSK =t2.TRPARNTTSK
and t3.TRCHILDTSK =t2.TRCHILDTSK
and t3.TRPRSSEQ =t2.TRPRSSEQ ));
commit;

But, they hardly inserted a row (I'm keeping them here for next time that I need to run them).

I eventually found the following:
https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=403346239050589&id=654116.1&_afrWindowMode=0&_adf.ctrl-state=41yvw5yrg_4

This was all about task view 29 (Which is the tools task view in 812 and above).  this was not secured, it did not EXIST!  I added task view 29 (because I could get to P9000) and then was able to see all of the proper menus.

OneWorld client install DEV client THICK client FAT client

Lots of things with E1 have got easier.  SM makes tools releases easier.  We makes client deploys easier.  9.1 makes viewing graphs easier (OVR).  Why is the fat client SO difficult!!

See the following link for trooublshooting the fat client / dev client install issues

https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=362486124486969&id=1085806.1&_afrWindowMode=0&_adf.ctrl-state=ywdtqyeuj_244

Then also check the guides
http://docs.oracle.com/cd/E24902_01/doc.91/e18843/toc.htm

I'm currently having a problem that I cannot select any packages because of "invalid package".  This is true for all of the packages that I built and the ones that I did not.  It's frustrating with all of the different 

When installing the oracle database piece, I get the following:
"The command failed with error 1."

So, I then found a 9MB file in c:\program files\oracle\inventory\logs\cloneActions..

This said "service already exists...", Arrgghh that's right.  I cleaned up the reg and all files, did not delete the services from the previously aborted install.  Remember to run regedt32 to get rid of the correct 32 bit settings.

sc delete < SERVICE name>
sc delete oracleE1LocalClrAgent
sc delete oraclemtsrecoveryservice


I again delete c:\oracle (not default location without giving you an obvious choice (you can edit settings.ini on the deployment server (under oneworld client install\thirdparty\oracle) and change the default DIR...

Delete registry settings

delete c:\oracle; c:\program files\oracle; c:\program files x86\oracle

restart

try install again, this is using the \\oneworld client install\installManager.exe, as this is the one that installs the DB component.

Steps required

1. Running Install Manager
2. Installing a local database
3. Installing a local database client
4. Installing the Development Client (this uses the Oracle Universal Installer (OUI)).
5. Troubleshooting any installation errors.
   
   
   Running the Development Client.

So, it's finally working - but... I get

INFO: Command = oracle.sysman.ccr.configCCR.CloneCCRPlugIn 
Command = oracle.sysman.ccr.configCCR.CloneCCRPlugIn has failed  
Configuration assistant "Oracle Configuration Manager Clone" failed 

This is sooo Frustrating.

Find https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=375569752832977&id=1439675.1&_afrWindowMode=0&_adf.ctrl-state=75s8l64k0_34 which tells me that I need the 32 bit client installed first.  Oh man...  I thought that this was for the deployment server only.

Right, follow the delete instructions once again and then install the 32 bit client and then 64bit OEE, CNC is such a joy sometimes.  I did skim read the instructions listed above and I did NOT see this jump out at me...

Okay, all of that is done and we have one more hurdle.  The install logs started to complain that there was no sbf.CAB file to be found (I must have selected to build BSSV - and have done no config).  I removed this from the DV910FC.inf file in the package_inf folder, and BOSH...  install begins.

More installation tips

I really do not like coming into an install that is 75% done.  The problem with this is that generally NOTHING works and you tend to doubt everything that you do, but hey…

If you are installing or upgrading JDE on unix, you need to know how to quickly determine if you are locking out user accounts.   It’s a common problem to have separate passwords between old and new systems (in the case of an upgrade) and get some typing wrong.

Remember to use the [DSPWD] in the jde.ini on the deployment server.  JDE expects that the passwords are the same as the OWNERNAME and the password is in CAPITALS (well this is the case for 9.1. 

you can use “SELECT USERNAME, ACCOUNT_STATUS from DBA_USERS’ to see the accounts and their status.  You could also SPECIFY a value like ‘ACCOUNT_STATUS <> ‘OPEN’

I also had a strange situation that the PRODDTA user could not insert into the TESTDTAT tablespace (strange yes), I had to “GRANT UNLIMITED TABLESPACE to PRODDTA’.