Create a readonly LDAP Bind DN with Oracle OID

Although the Oracle Directory Manager is a powerful tool, as the application server administrator you will probably find it easier to use the web based tool oiddas or the OID Self Service Console.  The OID Self Service Console (SSC) is part of the Delegated Administration Services.  This tool is much easier to use when managing a user. 

1. login to Oracle Identity Management Self-Service Console(OIDDAS)

To access SSC,open your browser and point to the infrastructure OHS port, and add the oiddas directory to the URL.

http://your-domain:7777/oiddas/

2. Once you click login, since our environment is a SSO-Enabled environment. It would
transfer you to the SSO login page. Here you have to use the orcladmin binding
account.

3. Click OK, then you would be able to login to oiddas like below:

4. Click Directory tab on this page

5. Click Create to create a new user called readonly. Fill in the basic information
of this user.

6. Once you click submit, you could be able to search out the user under the
directory

7. Click privileges to set the required permissions for this user. For now, we don’t
set anything in order for it to be read only.

8. Test if we could use the account to bind to our current LDAP Server.

Possible Issues and solutions:

This issue is because DSA service is not started. Check the status of the current settings.See the pic below:

But in fact, when you use ./opmnctl startall
The components: DSA, LogLoader,dcm-daemon WON’T be automatically started. You have to start them one by one by using the following command:

opmnctl startproc ias-component=dcm-daemon
opmnctl startproc ias-component=dsa
opmnctl startproc ias-component=LogLoader

Advertisements

Improve Database performance by analyzing tables

A little bit background why we need to analyze tables, indexes, clusters:

         Sometimes we have changes for the tables, for example if we have regular ETL process that constantly modifies the database structure or table contents. The statistics that Oracle collects from the last table analyzation might be out of sync with the current data dictionary. And this information is often used by the optimizer when querying. Therefore querys base against the old statistics would run slow and the database performance could be decreased because of the changed statistic not being collected accordingly.

       What statistics does Oracle collect by the analyzation?

         Oracle will collect statistic on the number of rows, the number of empty data blocks, the number of blocks below the high water mark, the average data block free space, the average row length, and the number of chained rows in a table when the Oracle ANALYZE TABLE command is performed.Oracle ANALYZE TABLE can be used to collect statistics on a specific table.

          When using Oracle ANALYZE TABLE all domain indexes marked LOADING or FAILED will be skipped.

          Oracle will also calculate PCT_ACESS_DIRECT statistics for index-organized tables when using Oracle ANALYZE TABLE

         Note:Before analyzing a table with the Oracle ANALYZE TABLE command you must create function based indexes on the table.

        Two options for analyzing tables: Computes Statistics VS Estimate Statistics

Computes Statistics Estimate Statistics
Method Full Table Scan Sampling
Accuracy High Depends on the sample
Cost High Low

        Computer Statistics Analyzation uses the full table scan again the entire Oracle table. Upon completion of the command, the data dictionary would be updated with the highly accurate statistics by the cost-based optimizer.However, Estimate Statistics Analyzaiton would take samples from the table and the samples are stored in the data dictionary.

       Note: You need to weight the time and database resource against the accuracy of the statistics before considering which method should take at your current point of view. For small to medium tables, use analyze table table_name compute statistics. For larger tables take a sample (for example, analyze table table_name estimate statistics sample 20 percent).

         Utilize TOAD for table analyzations

          Login Toad with your database connection. Select the table you want to analyze and right click on Analyze Table.

  

    Once you get to the analyzation page, select the tables you want to do analyzation on and click on the green arrow to start analyzing the table.By default the method would be estimate statistics as shown in the picture below.

The success information is displayed after the analyzation.

You could also change the mode to be compute statistics by switching to the Options tab and modify the Analyze Functions as shown below:

              Utilize SQLPLUS for table analyzations

             Login to SQLPLUS with your database connection as below:

            A better solution : Utilize DBMS_STATS to collect statistics

Here is a code snippet for how we are gonna invoke the dbms_stats package in 10g.Just note that Oracle Analyze table commands are now considered to be old fashioned. And the dbms_stats package are used more and more frequently in packages because it provides high quality infomation about tables and indexes.

BEGIN

FMIS_SYNCH.SYNCH_REF_TABLES_WITH_FMIS;

DBMS_STATS.GATHER_SCHEMA_STATS

(

ownname=>'yourowner',

options => 'GATHER AUTO',

estimate_percent => DBMS_STATS.auto_sample_size,

method_opt=>'for all columns size repeat',

cascade => true,

degree=>12

);


END;

For a more detailed information on the features that Oracle 11g Database has offered. Please take a look at the guru’s blog as below:

http://www.dba-oracle.com/art_builder_dbms_stats.htm

Dropping Tables(Options and Recovery)

This blog is mainly for analyzing dropping table commands for Oracle SQL. You might have seen the dropping table commands like below:

   1.drop table table_name;   
   2.drop table table_name cascade constraints;
   3.drop table table_name purge;
   

and wonder what would Oracle Database do behind the scenes for dropping table commands?

Basically, if there are no keywords specified as the first command shows, Oracle Database would perform the following operations:

  • All rows from the table are dropped.

  • All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them. If table is partitioned, then any corresponding local index partitions are also dropped.

  • All the storage tables of nested tables and LOBs of table are dropped.

  • When you drop a range-, hash-, or list-partitioned table, then the database drops all the table partitions. If you drop a composite-partitioned table, then all the partitions and subpartitions are also dropped.

The first question is Why am I getting the error: ORA-02449: unique/primary keys in table referenced by foreign keys

Answer: The Oracle error is caused by the attempt to drop a table with unique or primary keys referenced by foreign keys in another table, or in other word, the table that is referenced as a parent table by a foreign key constraint in a parent-child relationship that established between two tables through a foreign key. Oracle does not allow to drop tables referenced by foreign keys of other tables without specifying the CASCADE CONSTRAINTS option in the DROP TABLE statement, or to drop the parent table without first removing the foreign key.

The solution and workaround for the error when you want to drop tables referenced by child tables, is to use the CASCADE CONSTRAINTS option in the DROP TABLE statement.

That is why we use: 2. drop table table_name cascade constraints;

Now the question is What if I accidentally delete the table and want to recover it back?

Here comes the keyword PURGE that differentiates a normal dropping command and a purged one. See the table below for comparisons between the two statements.

DROP TABLE WITHOUT PURGE DROP TABLE WITH PURGE
Release the space No Yes
Rollbackable Yes No

Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user’s space quota. Additionally, the first command only results in the to-be-dropped table and its dependent objects being placed into the recycle bin. Specify PURGE if you want to drop the table and release the space associated with it in a single step.

Note: You cannot roll back a DROP TABLE statement with the PURGE keyword clause, nor you can recover the table if you have dropped it with PURGE clause. So be cautious and intentionally.

So how could we recover the tables in the recycle bin?

One feature of Oracle 10g is recyclebin, which allows you to recover dropped tables.With recyclebin, any tables you drop do not actually get deleted. Instead, Oracle renames the table and its associated objects to a system-generated name that begins with BIN$.

Look at the following example to get a better understanding:

Let us first create a testing table called studentinfo that contains one row as below.

Now we want to drop this table and look into recycle bin to find it.

You could see the object name is changed because of the dropping action.

You could still query it as you would do before with this new object name. Because the table data is still there and if you want to undo the drop action, you could use the FLASHBACK command like below:

The next time when we drop the same table, oracle database would associate a new object name for the table. So you would understand that after a table is dropped, it is just renamed. It is still part of your tablespace, and this space counts against your user tablespace quota.This space would not release until you purge the table out of the recycbin.

So now goes my last question: Will the dropped tables stay in the recycle bin forever if there is no action made toward it later on?

The answer is NO. When your tablespace runs out of space or until you reach your user quota on the tablesapce. At that point, Oracle database would purge the objects at one time, starting with the ones dropped the longest time ago, until there is enough space for the current operation.

Deploy application to Managed Server on 11g WebLogic Server (Part 2)

If you have finished setting up the managed server in the last blog post, you might run into a sip server error while starting the server as below:

Refer to Deploy application to Managed Server on 11g WebLogic Server (Part 1) if you need to catch up on the topic.

Error Code:”Parsing Failure in config.xml: java.lang.AssertionError: java.lang.ClassNotFoundException: com.bea.wcp.sip.management.descriptor.beans.SipServerBean

The reason why you can not start the managed server Server-1 is that the com.bea.wcp.sip.management.descriptor.beans.SipServerBean is not in classpath yet. Go to $WL_HOME/common/nodemanger and edit the file nodemanager.properties and set the variable StartScriptEnabled=true instead of the default false. Restart nodemanger and weblogic server. After that, you could successfully start the new mangerd server.

Deploy an application on to the managed server.

Click on Deployments under base_domain and then click Install under the Deployments table.

Specify the path where your deployment file would locate and then click next.

On the next page choose: Install this deployment as an application and then click next.

Now it comes to the page where we can choose our target server to deploy on. Here we choose Server-1.

I will go by defaults on the next two pages and then click Finish. You could adjust a bit according to different environments. 

After the new application is deployed, we could go back to Deployments table and check the state of the new application. If it were a successful deployment, the state should turn to be Active. Now it shows Failed meaning something is wrong during the deployment.

By looking at the log files, either nohup.out or Server-1.out,you might find similar errors like this:

Error Code:”Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: btm] Unable to build EntityManagerFactory

This is caused by NOTenabling JPA 2.0. My newapp needs to be running on top of three extra library files:hibernate-jpa-2.0-api-1.0.0.Final.jar,antlr-2.7.6.jar,commons-lang-2.4.jar. I will firstly copy these three jar files to $WL_HOME/common/lib/.In order to include them on to my managed Server Server-1, there are two ways to do that.

 Method 1

   Set the PRE_CLASSPATH to load extra jar files

   There are two places to set the PRE_CLASSPATH, which you have to take into consideration. If you only want to enable those library files on to your managed server, in my case Server-1, then just set the PRE_CLASSPATH in commEnv.sh which is under the directory:

$WL_HOME/common/bin

Insert the following statements into your commEnv.sh.

PRE_CLASSPATH=$WL_HOME/common/lib/hibernate-jpa-2.0-api-1.0.0.Final.jar: $WL_HOME /raid/middleware/wlserver_10.3/common/lib/antlr-2.7.6.jar: $WL_HOME /raid/middleware/wlserver_10.3/common/lib/commons-lang-2.4.jar

Export PRE_CLASSPATH

Note: If you also want to deploy your application on to AdminServer. You also need to set the same PRE_CLASSPATH in startWebLogic.sh under:

$DOMAIN_HOME/startWebLogic.sh

   Set your managed server staging mode to be nostage

After you restert the managed server, you would see the status of the newly deployed application should automatically change to Active.

 Method 2

By setting up the PRE_CLASSPATH in commEnv.sh, that could include all the library files in it for any managed server you created. Suppose you need a more flexible and server-oriented method that for different managed server, you could include different library files. Instead of throwing all necessary library files in to PRE_CLASSPATH, you could also set the boot path. Let us talk about how to do that from frontend.

Go to the servers table and click on the managed server you just created.

It will take you to the settings for your newly created managed server, in this case “Server-1”. Go to Configuration->Server Start. Scroll down to the field of arguments. That is where you will put your path of your library files.

The arguments should look like below:

-Xbootclasspath/p:$WL_HOME/common/lib/hibernate-jpa-2.0-api-1.0.0.Final.jar: $WL_HOME /common/lib/antlr-2.7.6.jar: $WL_HOME /common/lib/commons-lang-2.4.jar

By adding bootclasspath, JVM would load these files with a bootstrap classloader, which is separate from the system class loader. In this case before JVM even looks at the user defined CLASSPATH, those jar files mentioned in the bootclasspath would have already been loaded.

This method also requires the server staging mode being specified as nostage.

Deploy application to Managed Server on 11g WebLogic Server (Part 1)

This post is about the experiments I have done with 11g WebLogic Server recently. I was trying to deploy an web application on to a managed server instead of the default AdminServer. The main purpose is to have my web application deployed on to multiple servers that could be managed separately. I would break this topic into several blog posts in order to document as many pitfalls I have fell into for a better knowledge share. During the experiment, I ‘ve encountered with different types of failures that contained a lot clues which finally led me to the right path to a successfull deployment. Let us firstly start with some basic concepts of the terminologies involved and after that we would talk about more hands-on experience with it.

Kumar has this great post below to discuss the concepts briefly and clearly enough. Take a look at this post firstly if you want to kind of understand why we would configure the way as below:

  http://onlineappsdba.com/index.php/2008/07/24/domain-administration-managed-server-cluster-in-oracle-weblogic/

Create a new Managed Server on 11g WebLogic Server.

Firstly, log on to Administration Console. And then go to Servers under base_domain->Environments. Click on New at the servers table to create a new managed server.

Since AdminServer is taking the port 7001. I now switch to 7002

Then click Finish!! So now I have built one managed Server called Server-1 and I am trying to start the server. You might run into warning messages like below:

This message literally says you should associate the newly created managed server Server-1 with a physical machine and also reminds you that you have to start your Node Manager as well. So let us start with creating a physical machine and then associate the server with it.

Create a new physical Machine on 11g WebLogic Server.

Navigate back from the left panel which is under base_domain, click Machines.

Click New to add a new physical machine.

Fill in the information according to your platform. I call it Machine-1 for an example.

Enter the Listen Address.Mine is just the localhost. And then click Finish.

 

Now we have a physical machine ready to use.

Since Machine-1 is created successfully. And we are going to associate this physical machine to Server-1. Go back to Servers and click on the name Server-1 under servers table.

Now you would see Machine-1 in the drop down list and click on it to apply the machine. Navigate down to the bottom and click Save.

Go back to servers table and you would see the machine applied to the newly created managed server in the table.

Now we need to start Node Manager. Go to the terminal and access the path below:

$WL_HOME/server/bin

nohup ./startNodeManager &

Once the node manger is started and we could go back to frontend and start Server-1

 To be continued with problems starting Server-1 caused by sip server errors

[OBIEE Solution]Deployment of OBIEE 11G BI Publisher reports

Before we start discussing about the method on deploying OBIEE 11G BIP reports. Let us firstly look back to the 10g deployment of BIP reports. For example, we have two servers A and B. And we are trying to deploy the “BIPreports” under the xmlpserver repository on Server A to Server B.

Usually on 10g BIP, the default location of xmlpserver respository looks like below:

 <OracleBI_Home>/xmlp/Reports

So basically for a full deployment of the example we mentioned above. We could simply copy the BIPreports folder from the above location on Server A to Server B and then restart the BIP service. After that you could be able to see the BIPreports is deployed on to the new server and you could start view/edit the reports by login to xmlpserver.

However on OBIEE 11g, BI Publisher reports are integrated into the BI answers catalog, which means if you want to see the actual catalog you have to go through BI Answers. It still maintain an xmlpserver repository with the location:

 <middleware_home>/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports

We might think of using the same methodology to deploy the OBIEE 11g BI Publisher reports by just copying the reports to the above location. And upgrade the BIP catalog to BI Answers catalog.This would work if you upgrade the 10g reports to 11g reports and then deploy them to the folder above.

What you need to do next is to go to login to analytics.On the upper right corner, click Administration link:

Then on the next page, click Manage BI Publisher on the panel.

    Click Server Configuration on the Administration Page.

   

    On the next page click the button Upload to BI Presentation Catalog. And then click Apply.

  

   In this case you would be able to see the new folder and the reports underneath it once you login to analytics. Refer to previous blog on how to migrate 10g BIP reports to 11g   

   But this method didn’t solve the original question that if we want to get the reports from an 11g server and deploy them to another 11g server.We need to know after we upload the xmlpserver repository to BI Presentation Catalog. What happened?

    Well, it reformats the BIP repository to an BI Presentation Catalog folder entry. Say the folder BIPreports would be reformatted to folder BIPreports/ and BIPreports.atr. The reports under the original folder are also reformatted. The most obvious detection is by adding a atr file for each report and folder and sometime renaming some of the reports.The location of the BI Answers catalog is :

  <middleware_home>/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog/SampleAppLite/root/shared

   The problem is that you cound not simply copy the uploaded BIP reports folder to the same location on to another server. The presenation service would not recognize the folder as what 10g xmlpserver would do. And also once you have modified the uploaded reports from presentation service, the changes go directly to the catalog not the xmlpserver repository. And there is no way to reverse the BI Answers publisher reports back to the xmlpserver publisher reports having the changes we just made.

   In order to solve the problem, there are two ways available both with the help of presentation service. On OBIEE 11G, oracle tends to put more heavy usage on the frontend operation instead of backend manipulation.Good or bad? You tell me.

   Method 1.

   If you want to deploy a whole folder of BIP reports from OBIEE 11g Server A to B, simply login to analytics on Server A.

   Click Catalog:

  

   On the left panel, expand shared folders and choose the perticular report folder that you want to deploy to Server B. For example, I would do Sample2. Click on the folder and then click Archive

  

   Now depends on if you want to remain the permissions and timestamps. Check the boxes and click ok.

 

  Click Save. Then this archived file contains all the reports under Sample2 and is ready to be deployed to Server B.

 

  Login to analytics of Server B. And go to catalog as the step above.Choose the folder where you would like to deploy the reports. In this case, I would choose shared folder. And then click unarchive.

  At the pop-up window. Choose the Sample2.catalog and then input the neccessary parameters.Click OK

 Now Sample2 is showed under the shared folder and all reports are under it.

  Method 2.

  The second methodology for deployment of BIP reports is that you could simply download and upload a single report. This is useful when you are not deploying too many reports.

  Instead of login to analyitcs, this time you need to login to xmlpserver on Server A at:

  http://domain_name_onServerA:9704/xmlpserver

  Step 1.Click Catalog on the right corner to traverse down to the reports.

  

Step 2. Navigate to the report you want to move to another server. Click on More->Download

You have to download the data model correspondingly as well like below:

Now the files that we have just downloaded are looking like this:

    Balance Letter.xdoz

    Balance Letter Datamodel.xdmz

Step 3. Go to the Server B and upload the files. Login to the following link:  

http://domain_name_onServerB:9704/xmlpserver 

Go to catalog and navigate to the folder you would like to upload to.

At the left corner, click upload

Upload the two files we just downloaded to Server B and remember to associate the template with the data model.

[java.daily.notes]Use Firebug to debug Javascript on the fly

Step 1. You should have firefox browser handy. If not, you could download one from :

        http://www.mozilla.org/products/download.html?product=firefox-11.0&os=win&lang=en-US

Step 2. Once you get your firefox browser, you could then download firebug within the browser.

       Open Firefox browser and go to Tools-> Options

      

 

       Go to General and then click Manage Add-ons

    

      Look for firebug in the search criteria. And then click Install besides the plugin.

   Step 3.  Now you have firebug installed, you could start using firebug to debug the Javascript. You could find a small icon aside on the right corner of the browser and once you click that button, the firebug is invoked . However you could only debug one Javascript a time for one session.

       

   Normally firebug would show up at the bottom. When you choose Script, you have to click the blue link : Enable

 

    Now click Reload

   

 

     Say I navigate to the Javascript for senstive area that I would assume where a problem would be. And put the breakpoints on the left like below: You now see the buttons on the upper right corner are still greyed out because you have not invoke anything on the current web page.

  And when you invoke the corresponding fields on the webpage. It dynamically breaks to the line you set the breakpointes. Now you are ready to debug the Javascript step by step by clicking on the buttons on upper right corner.

      Like most of the debug tools. You could do mouse over to a perticular variable and you could validate from there and on the right panel. You could add or remove breakpoints and  watch the values of different variables. It is super useful for Javascript coders.

     Have fun and this is end of Instructions.Yay!!