Oracle Expert

 Database Preparation

• Check that dba_lock exists. If not, run

sqlplus / as sysdba

@?/rdbms/admin/catblock

• Create tablespace APEX

 

CREATE TABLESPACE apex

DATAFILE ‘/uxx/oradata/xxxx/apex01.dbf’ SIZE 500M REUSE

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;

 

• check that shared_pool_size is at least 100M
• check that system tbs has at least 85mb free
• Check that Oracle Text is already installed

Select * from dba_registry;

 

• If not, let’s install it then

sqlplus / as sysdba

@?/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK

 

where ctxsys is the install schema, SYSAUX is the default tablespace, Temp is temp tablespace for that user,

and NOLOCK instructs the script to not lock the account when the install is complete (lock after!)

 

– not install the language bit (UK for uk,or US for us)

connect ctxsys/ctxsys

@?/ctx/admin/defaults/drdefuk.sql file (for UK).

 

• Check that Oracle Text is already installed

 

Select * from dba_registry;

 

CONTEXT

Oracle Text

10.2.0.3.0                     VALID

01-DEC-2008 09:46:01          SERVER

SYS                            CTXSYS

VALIDATE_CONTEXT

 

Install Oracle HTTP Server.

 

vi /u01/app/oracle/oraInst.loc_10g_httpd

inventory_loc=/u01/app/oracle/oraInventory_10g_httpd

inst_group=oinstall

 

mkdir –p =/u01/app/oracle/oraInventory_10g_httpd

 

export ORACLE_HOME=/u01/app/oracle/httpd/10.2.0

 

 

 

Find the companion CD

./runInstaller –invPtrLoc /u01/app/oracle/oraInst.loc_10g_httpd

 

Select Product to Install: Oracle Database 10g Companion Products 10.2.0.1.0 ( do not select HTML DB)

Target directory: /u01/app/oracle/httpd/10.2.0

 

Don’t forget to select Apache to install

 

Sort out images

mkdir –p :/u01/app/oracle/httpd/10.2.0/Apache/apex_images

scp –pr xx:/u01/app/oracle/httpd/10.2.0/Apache/apex_images/images .

ln -s /u01/app/oracle/httpd/10.2.0/Apache/apex_images/images

/u01/app/oracle/httpd//10.2.0/Apache/Apache/htdocs/i

 

Setup DAD

vi /u01/app/oracle/httpd/10.2.0/Apache/modplsql/conf/dads.conf

 

alias ‘i’ “/u01/app/oracle/httpd/10.2.0/Apache/apex_images/images”

AddType text/xml xbl

AddType text/x-component htc

 

<Location /pls/apex_ucig04>

Order deny,allow

PlsqlDocumentPath docs

AllowOverride None

PlsqlDocumentProcedure wwv_flow_file_mgr.process_downloadd

PlsqlDatabaseConnectString hostname:1550:DB_UAT.UK.HIBM.HSBC ServiceNameFormat

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8

PlsqlAuthenticationMode Basic

SetHandler pls_handler

PlsqlDocumentTablename wwv_flow_file_objects$

PlsqlDatabaseUsername APEX_PUBLIC_USER

PlsqlDefaultPage apex

PlsqlDatabasePassword APEX_PUBLIC_USER

PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize

Allow from all

</Location>

                         

Managing Apache services

/u01/app/oracle/httpd/10.2.0/opmn/bin/opmnctl stopall

Start OPMN service

/u01/app/oracle/httpd/10.2.0/opmn/bin/opmnctl startall

Check Status

/u01/app/oracle/httpd/10.2.0/opmn/bin/opmnctl status

 

Processes in Instance: IAS-X-nsudd142.6299

——————-+——————–+———+———

ias-component      | process-type       |     pid | status

——————-+——————–+———+———

HTTP_Server        | HTTP_Server        |   19197 | Alive

LogLoader          | logloaderd         |     N/A | Down

dcm-daemon         | dcm-daemon         |     N/A | Down

 

To stop and restart HTTP_Server component

cd /u01/app/oracle/httpd/10.2.0/opmn/bin

./opmnctl stopproc ias-component=HTTP_Server

opmnctl: stopping opmn managed processes…

./opmnctl startproc ias-component=HTTP_Server

opmnctl: starting opmn managed processes…

To restart

./opmnctl restartproc ias-component=HTTP_Server

opmnctl: restarting opmn managed processes…

 

 

 

Install APEX

cd to apex media directory

 

check that LD_LIBRARY_PATH is set correctly

 

sqlplus / as sysdba

@apexins apex apex TEMP /i/

 

Check that installation is finished successfully

SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = ‘APEX’;

This should show VALID.

If it has been unsuccessful, normally you are left with status of LOADING.

 

 

 

If installation failed, to start again

 

ALTER SESSION SET CURRENT_SCHEMA = FLOWS_020000;

exec flows_020000.wwv_flow_upgrade.switch_schemas (’FLOWS_030100′,’FLOWS_020000′);

 

DROP USER FLOWS_030100 CASCADE; (might require a shutdown force restrict if hangs)

 

Then restart with apexins again

 

Change APEX admin password

cd to apex media

sqlplus / as sysdba

@apxchpwd.sql  (and enter new password)  

 

URL

http://hostname:7777/pls/apex/apex_admin

 

for users

http://hostname:7777/pls/apex/

 

 

Checking port inside db:

select dbms_xdb.gethttpport from dual;

To set a new port:

exec dbms_xdb.sethttpport(7780)

To disable:

exec dbms_xdb.sethttpport(0)

 

 

Maintenance

How to make sure apache gets restarted automatically ….

 

 

 

I have been dealt a new challenge, i.e. to improve performance on a risk system which data volume will increase by 10 fold(!), while the allocated processing window will stay the same. The additional complication is that delivery timeline is within 8 weeks, and with minimum allocated development resource.

With the current volume, the system would just make the SLA. Any additional load will completely push the processing time past the agreed 8.30AM SLA.

Facts:

• data feed is delivered between 5AM and 6.30AM
• currently, data is supplied on position level, soon to be changed to trade level, resulting in 10x increase in volume
• risk reporting must be available to users by 8.30AM

For the last few years, financial institutions have embraced “follow the sun” support model.  The objective is to provide the best customer support, without paying the standard hefty price tag if the 24/7 support had been provided by local UK staff. UK teams have enjoyed the support of remote teams in North America, Brazil, India and China. 

This has been adopted by many functions within the organisation, including DBA teams.

At the beginning, the tasks trusted to the remote DBAs have been straightforward. Maintain smooth running of a database, mainly backups, space management.

UK DBAs would still perform other tasks, such as installations, new database server configuration, database upgrades, RAC configurations, standby database setups.

However, as years go on, remote outsource DBAs have strengthen their staff, and with their increased experience and knowledge, they are ready to taken on more and more responsibility. Outsourced teams have the challenge of retaining valuable members of staff, who are hungry to do more interesting things, who are no longer content to be asked to perform routine day to day support functions. 

And why can’t they? Why wouldn’t remote outsource DBAs perform server configurations, database installations, upgrades, RAC, standby, you name it. 

Would this mean there is no more valid requirements for financial institutions to have local DBA teams in the UK? With the fast broadband available around the world, it no longer matters when one sits when providing the support functions.

With the economic downturn, surely this question has been asked both by the management team, as well as by the UK DBAs themselves. And if the individual DBAs are not prepared to bring more value to differentiate themselves from their counterparts in India or China, they are right to be worried about their job security.

However, organisations should encourage and support their DBAs to help the banks get value out of their investment in Oracle.

What advantage do UK DBAs have over their remote counterparts?  In my opinion, the answer is: close proximity to the users. They should leverage this advantage by building close relationships with the business. As outsourced DBAs are getting really good technically, UK DBAs should emphasise on improving their skills that can only be gained through close interaction with application/business users. Something that can not easily be picked up through standard training courses. 

A lot DBAs express their frustration on the quality of Oracle implementations delivered by development team. Well, now there is no better time to change that around. Muck in. 

If I am running a Global DBA team and looking to save cost and maintain quality, I would certainly continue and increase the use of outsourced team to provide reliable 24/7, and support the UK individuals who are ready to embrace the outsourcing culture and push themselves out of their comfort zone, try to understand challenges that are faced by the business, the application, and the development team, and use their expertise in Oracle to help the banks run better.

My father in law, William Davies, is turning 80 next week. We were supposed to go to Cornwall to visit Rick Stein’s Seafood Restaurant to celebrate, but since Molly (m-i-l) has not been feeling too great, we have now cancelled the trip and will go to celebrate more locally instead at the River Cafe in Hammersmith.

I am panicking a little bit, because the birthday is 4 day away, and we still have not bought him any present yet. I will be working in the Claromentis office in Brighton next Tuesday, so hopefully will be able to drag Nigel out for a bit to go pressie hunting. Cutting it a bit close, but there you go ….

In large financial organisations, there are normally separate entities responsible for each separate areas/components within IT.Network team, UNIX team,Windows team, Oracle team, Sybase team, Storage team and the list goes on.

However there is nothing more frustrating than dealing with an issue that falls in the grey area of responsibility between groups, and when noone would claim ownership of the issue, leaving the business exposed. A simple issue, which falls under this category, could take weeks to resolve, simply by lack of ownership. The challenge here is to define the problem clearly, notify and engage relevant cross teams, highlight risks and find a solution.

Unfortunately, when ownership of a problem is unclear, there is a tendency to point a finger or cast a blame on someone else. Rather than the usual “not my problem” shoulder shrug, a more refreshing approach would be to take the opportunity to learn about how these different departments interlink, enhance our own problem-solving ability by facts findings and at the end, posses the most information about the issue, and how to solve it.

I experienced this first hand, when by chance I spotted a large, relatively high profile Oracle database which has not had a successful full backup (to tape) for weeks. The normal course of action by DBA team is to rerun backup jobs until it is successful. The problem with this particular database is that even on a sunny day, a full backup will take up to 24 hours to complete. So reruns have been clashing with incremental backups. A mess.

So, escalated issue to the DBA team, Tape Storage Team, Business/Application team, Infrastructure Manager. The initial reaction was quite disappointing :

• Business/Application team: “this is a DBA problem”
• DBAs: “this is a tape storage problem”
• Tape Storage team: “you are backing up a ridiculous amount of data to tape over the network, what do you expect?”

It took a lot of emails, phone calls, instant messaging to engage everyone and to get everyone to realize that there is not a single team solely responsible. The point is, there is a problem which needs fxing, and it should be of everyone’s interest to get it fixed asap.

I am sure we will get this solved at the end. I am a Oracle Data architect with 18 years experience specialising in design and performance tuning. And I wonder how I got involved in this and ended up spending 2 days chasing up a backup issue. I suppose because I care and someone has to do it.

Well, the credit crunch is here, and those of us working in the financial area are already feeling the direct impact. First, the feared job losses, followed by budget shortfalls. IT divisions are particularly feeling the heat.

• Operational areas not only are now operating with smaller number of DBAs, but also by exchanging experienced (but expensive) contractors with less experienced members of staff.
• Application teams are focusing more on BAU (Business as Usual), maintaining skeleton staff for each application area.
• The ratio of reduction in the number of developers perhaps is larger than the ratio of reduction in development work raised by the business demands.

Job cuts certainly save cost. The danger is if it results in extreme compromise in quality of support and development products due to unrealistic workload demands on already overworked staff.

The other avenue for cost cutting is server consolidation. Oracle charges its license by CPU. The more servers you have, the higher number of CPU accumulates, the more expensive it gets.

Rationalize your Oracle RAC domains. There are a lot of applications out there unnecessarily run on RAC configuration. Requirement for resilience is mistaken with requirement to run RAC. The difference? Well in money term, half the cost if you settle for single instance configuration, rather than RAC.

If you are planning to migrate an application to a bigger, better server, look again. Say, a high profile application seems to have outgrown its current environment, performance seems to degrade, SLA is being missed. A DBA team that is overworked, and isolated from the business/application team, would often agree to raise a request for a new server purchase, with more memory, more CPUs, more capacity.  But in some cases, this costly purchase, configuration, migration, downtime could be avoided with:

• DBA team working closely with Application team
• Understand hot spot during a working day
• Is an Oracle backup interfering with the smooth running of a batch?
• Is a UNIX file system backup interfering with the application?
• Are maintenance work done at the wrong time on the server, thus impacting application performance
• Do application team have enough Oracle expertise to make the most out of their oracle database?
• Perhaps a tuning exercise is needed

select name,value-243956081 from v$sysstat where name like ‘workarea executions%optimal’
union all
select name,value-617354 from v$sysstat where name like ‘workarea executions%onepass’
union all
select name,value-602411 from v$sysstat where name like ‘workarea executions%multipass’

select * from v$sysstat where name like ‘workarea executions%’

al 243707814
workarea executions - onepass 616835
workarea executions - multipass 602193

select 720/240981*100 from dual

select (616835+602193)/243707814 from dual

NAME,VALUE
workarea executions - optimal,243822342
workarea executions - onepass,616872
workarea executions - multipass,602230

select * From v$pgastat

alter system set pga_aggregate_target=500m scope=memory

rem Subject: Calculation of HighwaterMark of datafiles
rem
rem Remarks: minimal size of a datafile is 2 Oracle blocks
rem resizing should always be a multiple of Oracle blocks
rem
rem Requirements: select on sys.dba_data_files
rem select on sys.dba_free_space
rem select on sys.v_$parameter
rem
rem
rem It may be necessary to change declarations of filesize and extsize
rem to NUMBER instead of binary_integer. Binary Integer can take up
rem values from -2147483647 to +2147483647.
rem
rem If you have a datafile of size 2GB and larger then size of the file
rem is 2147483648 bytes and larger and thus it can not be stored in
rem a binary integer. The respective variables could be defined
rem as number instead.
rem
rem ——————————————————————–

set serveroutput on
execute dbms_output.enable(2000000);

declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
where status !=’INVALID’ –and tablespace_name=’MRC_DATA_SMALL_ASSM’
order by tablespace_name,file_id;

cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;

blocksize binary_integer;
filesize binary_integer;
extsize binary_integer;

begin

/* get the blocksize of the database, needed to calculate the startaddress */

select value
into blocksize
from v$parameter
where name = ‘db_block_size’;

/* retrieve all datafiles */

for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
<>
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit outer;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line(’Tablespace: ‘
||’ ‘||c_rec1.tablespace_name||’ Datafile: ‘||c_rec1.file_name);
dbms_output.put_line(’Can not be resized, no free space at end of file.’)
;
dbms_output.put_line(’.');
else
if filesize < 2*blocksize
then
dbms_output.put_line(’Tablespace: ‘
||’ ‘||c_rec1.tablespace_name||’ Datafile: ‘||c_rec1.file_name);
dbms_output.put_line(’Can be resized to: ‘||2*blocksize
||’ Bytes, Actual size: ‘||c_rec1.bytes||’ Bytes’);
dbms_output.put_line(’.');
else
dbms_output.put_line(’Tablespace: ‘
||’ ‘||c_rec1.tablespace_name||’ Datafile: ‘||c_rec1.file_name);
dbms_output.put_line(’Can be resized to: ‘||filesize
||’ Bytes, Actual size: ‘||c_rec1.bytes);
dbms_output.put_line(’.');
end if;
end if;
end loop;
end;
/

–Use DBMS_METADATA, rather than DBA_SOURCE

set pages 0
set long 90000

SELECT substr(DBMS_METADATA.GET_DDL(’TABLESPACE’, tablespace_name),1,255) FROM DBA_tablespaces;

SELECT substr(DBMS_METADATA.GET_DDL(’USER’, username),1,255) FROM DBA_users;

SELECT ROWNUM, event, total_waits, total_timeouts, time_waited, average_wait,
max_wait, time_waited_micro
FROM v$session_event
WHERE SID = ‘18′