Hyperion Installation document

Hyperion High Level Installation Steps

Download PDF with Architecture Diagram. Hyperion Installation

Here I am explaining about Hyperion installation on a distributed environment (High Level). Since planning and reporting are expected to consume high resources we kept these services separated.

Hyperion Installation Steps.

On LinuxDBHyperion.appsonlinedba.com
1) Install Oracle Binaries (11gR2) . Since we planned to test cold failover we had installed the oracle binaries on cluster.
2) Create a dummy (blank) db using DBCA.
3) Install 11gR2 client.
4) Install Hyperion essbase. You need to deselect all the other components which are selected by default.


On LinuxappHyperion.appsonlinedba.com
5) Install Foundation(Shared Services) and Financial reporting . By default essbase and planning are selected , deselect them before proceeding with the installation

On WindowsHyperion.appsonlinedba.com
6) Installation Planning . Here you need to deselect other components which are checked by default.

Hyperion Configuration Steps

Create Schema:

We can install each service on different schema or same schema. To isolate each module we created separate schemas

Hss: stands for Hyperion shared services
Hbi: Hyperion Business Intelligence
heas: Hyperion essbase administration services’
hes: Hyperion essbase studio

Configure different services:
When you invoke the installer it asks you about which service to configure make sure you do it as below on each server. Also make sure you select the correct schema created above .

a) Configure Shared service ( LinuxappHyperion)
b) Configure essbase ( LinuxDBHyperion )
c) Configure Planning ( WindowsHyperion )
d) Configure Reporting ( LinuxappHyperion )

Thanks ,
Sandarsh Chavalmane

11gR2 RAC , GRID infrastructure ASM ,vote ,ocr bla bla.... and a big confusion before you implement these.

11gR2 RAC , GRID infrastructure ASM :

Since I did my first implementation, I was interested in sharing

1) Why is VOTE and OCR inside ASM?

Storing the OCR and vote disk inside ASM eliminates the need for third party cluster and volume managers and also helps eliminate the complexity of managing separate disk partitions for the OCR in oracle clusterware installations.
Better manageability. and also Oracle WANT to SELL RAC with ASM ,....

2) How does cluster start as vote disk and OCR are inside ASM???

In 11GR2, every node has a local registry called OLR. OLR is created during the installation and by default its located under $GRID_HOME/cdata/.olr
This file actually facilitates the oracle cluterware to startup process when OCR and vote are inside ASM.

During the startup OLR is referenced to determine the exact location of the vote disks. This will facilitate the node to join the cluster. After the initial phase ASM is started and the processes that require full OCR can start and the clusterware startup process completes.

3) SAN and NAS.

I always assumed EMC sells only SAN . But they too have NAS storage.Since we prefer to have block storage for oracle database we opted SAN for oracle software and NAS for usual NFS.

Also good that now there is a EM plugin avaliable to monitor EMC disks.


Thanks

Sandarsh Chavalmane

PCP in non-RAC instances - Pitfalls

It was a wonderful surprise when I stumbled upon Metalink Note ID: 743716.1, which details steps for configuring PCP in non-RAC instances.

This is very useful while using hardware or software clustering and not Oracle clustering (RAC).

We are using Veritas clustering (software clustering) for server failover. We were recently testing server failover and there are two things that I am unable to figure out as of now and which necessitates manual intervention rendering the whole failover mechanism manual:

1. How would the VNC server failover when using PCP with non-RAC?

Since, the hostname changes when the failover happens, the display variable would still be pointing to the failed server and not the failover server.

The work around to the above problem is obviously to reset the display variable and run autoconfig, which would mean that the failover mechanism is no longer automatic.

2. As soon as the failover happens, we need to make a manual entry in the Web Tier $TNS_ADMIN/tnsnames.ora to reflect the change in the concurrent processing server. This entry takes the form FNDFS_<FAILOVER_SERVER_NAME>.. Thankfully, this does NOT necessitate downtime of the instance.

Do post your comments/suggestions/ideas on the above 2 problems.

Issue while exporting /importing the oracle outlines from one database to other

On of the DBA complained that he had created 25 outlines in the test instance while tuning the bad sql's. Now he wanted to move the outlines to production.
One of the method to achieve this is to export the tables from the outl schema of the source (DEV) to target(PROD)
Both the export and import utilities executed without any issues.

25 rows were exported from DEV and then imported to source(PROD).

But when we count the dba_outlines table it showed 21 rows , 4 outlines were missing.


Below are the details

--------------------EXPORT COMMAND on DEV-------------------




exp / file=/tmp/outl2.dmp log=/tmp/outl2.log CONSTRAINTS=n TRIGGERS=n tables=outln.ol\$,outln.ol\$hints,outln.ol\$nodes grants=n

Export: Release 10.2.0.3.0 - Production on Mon Mar 23 18:26:03 2009



About to export specified tables via Conventional Path ...
Current user changed to OUTLN
. . exporting table OL$ 25 rows exported
. . exporting table OL$HINTS 488 rows exported
. . exporting table OL$NODES 733 rows exported
Export terminated successfully without warnings.




-------------------import COMMAND on PROD-------------------------

Import the outlines on the target

ibisdev-e2-zone02.east:PATCH4:/orahome $ imp / file=/tmp/outl2.dmp log=/tmp/outlBTCsingle.log CONSTRAINTS=n grants=n fromuser=outln touser=outln ignore=y commit=y

Import: Release 10.2.0.3.0 - Production on Mon Mar 23 18:28:26 2009


Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing OUTLN's objects into OUTLN
. . importing table "OL$" 25 rows imported
. . importing table "OL$HINTS" 488 rows imported
. . importing table "OL$NODES" 733 rows imported
Import terminated successfully without warnings.


---- below sql shows that there are only 21 outline on PROD -----

login to database

sqlplus "/ as sysdba"

select count(*) from dba_outlines;

Count(*)
------------------------------------------

21

------------------------------------------



Below you can see a difference in the header hint count .

If there is a hint ol$ hint count is less than ol$hints then the import utility will first import the data and then sync both the tables.
If the ol$ hint count is more than ol$hints then the data will be first imported and then deleted due to hintcount mismatch.

In other words

You need to ensure that you use a similar SQL statement to swap the counts, because if the hint count and the number of hints do not match, then one of the closing procedural steps in the import will
be to delete any plan which is internally inconsistent - i.e. where the actual count doesn't match the recorded count.


Only way to overcome this is to update the outln.ol$ hintcount to the value less than or equal to hintcount of outln.ol$hints

=================Below query on PROD shows that there are no hints mismatch between the tables==================

SQL> clear buffer
buffer cleared
SQL> 1 select a.OL_NAME,a.HINTCOUNT,count(b.OL_NAME)
2 from outln.ol$ a,
3 outln.ol$hints b
4 where a.OL_NAME = b.OL_NAME
5 group by a.OL_NAME,a.HINTCOUNT
6* having count(b.OL_NAME) <> a.HINTCOUNT SQL> SQL> SQL> SQL> SQL>
SP2-0734: unknown command beginning "6* having ..." - rest of line ignored.
SQL>
SQL> 6 having count(b.OL_NAME) <> a.HINTCOUNT
SQL> /

no rows selected

===============================Shows that there are only 21 outlines on PROD and has the hints in sync===========================================

SQL> l
1 select a.OL_NAME,a.HINTCOUNT,count(b.OL_NAME)
2 from outln.ol$ a,
3 outln.ol$hints b
4 where a.OL_NAME = b.OL_NAME
5 group by a.OL_NAME,a.HINTCOUNT
6* having count(b.OL_NAME) <> a.HINTCOUNT
SQL> del 6
SQL> /
CR_6788803_FNDWFBG 22 22
CR_6807075_CWP_05 9 9
CWP_ACCT_INFO_01 27 27
CR_6805769_PR_08 36 36
CR_6807045_CWP_01_OLD 17 17
CR_6805769_PR_02 17 17
CR_6788792_ARBARL 22 22
CR_6805769_PR_03 12 12
CR_6805769_PR_04 17 17
CR_6805769_PR_10 20 20
CR_6789052_FNDWFBG 22 22
CR_6794612_CWP_04 12 12
CR_6805769_PR_12 18 18
CR_6807240_AMSRFSEG 18 18
CR_6794623_CWP_07 12 12
CR_6797878_ITA_SETUP 25 25
CR_6805769_PR_06 12 12
CR_6807068_CWP_03 29 29
CR_6805769_PRICE_REPORT 17 17
CR_6805769_PR_07 12 12
CR_6809701_OE_EBFF 53 53

21 rows selected.





-----------------------------------COMMAND shows thate outlines that have hintcount difference on DEV---------------------------------------------

SQL> select a.OL_NAME,a.HINTCOUNT,count(b.OL_NAME)
from outln.ol$ a,
outln.ol$hints b
where a.OL_NAME = b.OL_NAME
group by a.OL_NAME,a.HINTCOUNT 2 3 4 5
6 having count(b.OL_NAME) <> a.HINTCOUNT
7 ;

OL_NAME HINTCOUNT COUNT(B.OL_NAME)
------------------------------ ---------- ----------------
CR_6807045_CWP_01 18 17
CR_6807062_CWP_02 23 22
CR_6807090_CWP_09 24 14
CR_6788803_FNDWFBG 22 24
CR_6789052_FNDWFBG 22 24
CR_6809701_OE_EBFF 53 54

6 rows selected.


--------------------------------------------------------------------------------------------------------
Still we can see only 4 outline from the above six are missing , it is because the ol$ hintcount is LESS than ol$hints in the .




===================SOLUTION=========================

----------perform the below steps on source(DEVELOPMENT)-----------


SQL>select hintcount
from outln.ol$
where
OL_NAME='CR_6807045_CWP_01';

HINTCOUNT
----------
18

SQL> update outln.ol$
set hintcount=17
where OL_NAME='CR_6807045_CWP_01';

1 row updated.

SQL> select hintcount from outln.ol$ where OL_NAME='CR_6807045_CWP_01';

HINTCOUNT
----------
17

SQL> commit;

Commit complete.

SQL> !
$

------------EXPORT COMMAND on DEV after making the hints on ol$ equal to ol$hints------------------------------------



$ exp / file=/tmp/outl2.dmp log=/tmp/outl2.log CONSTRAINTS=n TRIGGERS=n tables=outln.ol\$,outln.ol\$hints,outln.ol\$nodes grants=n

Export: Release 10.2.0.3.0 - Production on Mon Mar 23 18:26:03 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to OUTLN
. . exporting table OL$ 25 rows exported
. . exporting table OL$HINTS 488 rows exported
. . exporting table OL$NODES 733 rows exported
Export terminated successfully without warnings.


---------------------import COMMAND on PROD-----------------------



:/orahome $ imp / file=/tmp/outl2.dmp log=/tmp/outlBTCsingle.log CONSTRAINTS=n grants=n fromuser=outln touser=outln ignore=y commit=y

Import: Release 10.2.0.3.0 - Production on Mon Mar 23 18:28:26 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
. importing OUTLN's objects into OUTLN
. . importing table "OL$" 25 rows imported
. . importing table "OL$HINTS" 488 rows imported
. . importing table "OL$NODES" 733 rows imported
Import terminated successfully without warnings.



--------------------------VALIDATING THE FIX ---------------------

:/orahome $
me $ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Mar 23 18:28:40 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> select count(*) from dba_outlines;

COUNT(*)
----------
22


This shows that this time import has imported 22 outline (21 + the one which was fixed)

You may have to perform the workaround on all the outlines that have a hints mismatch (ol$ hintcount > ol$hints hintcount)


About EXPORT and IMPORT functionality with outline as an example


1) check - select * from EXPACT$ where owner = 'OUTLN'; - Import runs this to cleanup

Some of these rows are cleared out at later point by the packages called in order to ensure the internal consistency of the metadata.

The packages called are outline.drop_collision, outline.drop_unrefd_hints, outline.drop_extras. These packages are called as an Import/Export post table action and the main purpose of this procedure is to clean up after an import. The drop of outlines may be because they are simply not required in the target database to obtain plan stability. The definitions for all of these procedures can be found in dbmsol.sql. Taking
a look at the usage notes, there would appear to be some inconsistency which gets cleared up by these functions during export/import. This could explain the missing rows.


The outln$ table has a 'hintcount' column.

You need to ensure that you use a similar SQL statement to swap the counts, because if the hint count and the number of hints do not match, then one of the closing procedural steps in the import will
be to delete any plan which is internally inconsistent - i.e. where the actual count doesn't match the recorded count.


You might try establishing a db_link between the two databases and thendo an update using a slect from the other table. This would bypass the export/import actions that are deleting your new outlines even as itadds them. Another possibility if db_links aren't workable is to import into a different user then use the update using a select from that users tables.

Thanks, Sandarsh

Version of mod_security with EBS 11.5.10.2

Though it was a bit tough to find out the version of Mod_security that is shipped with E-Business Suite 11.5.10.2, I finally managed to do it, thanks to Metalink Fora!

The version that comes with eBS 11.5.10.2 is 1.8.4. Below is how you find out the version of mod_security shipped with your version of eBS.

$ strings $IAS_ORACLE_HOME/Apache/Apache/libexec/mod_security.so | grep mod_security/

Using GLOGIN.SQL

Many a time, DBAs and Developers tend to run scripts, accidentally, in instances that they do not actually intend to. In extreme cases, this might result in disastrous consequences, sometimes even leading to recovering/restoring the database from a backup.

This mistake can easily be avoided if the sql prompt displays the username and/or the SID in lieu of just "SQL>". This can be achieved using a variety of methods.

The easiest method (according to me) is detailed below.

Whenever sqlplus is invoked, 2 files are executed: glogin.sql and login.sql (in the order mentioned). These 2 files are located under $ORACLE_HOME/sqlplus/admin.

Modify the file glogin.sql and add the below line:

set sqlprompt "_user'@'_connect_identifier > "

The net result of this is that the sqlprompt will appear as below whenever you login to sqlplus:

system@ORCL>

where system is the username and ORCL is the SID.

Note: One drawback of this method, if it may be called so, is that whenever one logs in to sqlplus using /nolog option, only the '@' character is displayed as the connection to the database is yet to be established. As soon as the connection is established, the username and SID are again populated in the sqlprompt. Incidentally, this is a new feature in 10g, wherein the glogin.sql and login.sql files are executed after the connect command.

Table Fragmentation

Table Fragmentation

Found this good note on net:-
When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

Table size (with fragmentation)

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb

Actual data in table:

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb

Note = 72952 - 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition