9 Replies Latest reply on Jul 14, 2011 12:54 PM by Brandon Gillis

    Multi-site BDSSA Solution - Oracle DB's

      Share This:

      Hi everyone.

       

      We are working to implement a multi-site reporting solution using BDSSA. We have it setup and functioning with our primary site and are now trying to add the additional site. We hit snag in the documentation for adding the site. In the documentation it gives these steps for adding an additional site:

       

      1) Add the site using the "add site" command

      2) Set the site context in the ETL (ODI) database using the set context command.

      3) Create a linked server for the BMC BladeLogic core database (this is the problem)

       

      We are using Oracle Databases and the only instructions under the "Create a linked server for the BMC BladeLogic core database" are for MSSQL,  but it clearly states:

       

      "This step is required for SQL Server installations in preparation for installing the product and using the primary site. This step is required for all database types when adding sites after the product installation."

       

      The instructions underneath that say this at step 2 on page 172:

       

      "From the reports warehouse database location, log into SQL Server Management

      Studio as sa."

       

      What are we supposed to do when it is Oracle?

        • 1. Multi-site BDSSA Solution - Oracle DB's

          Hi Brandon,

           

          I am sorry for the missing information. I'm in the process of getting the info from development and will update the document for the next release. Here is what I have from development so far:

           

          To create Linked server for Oracle -

           

          1] Login with warehouse user credentials.

                                

          2] Give the command as -

                                create database link <Linked server name> connect to <OM DB name> identified by <OM DB password> using '<OM DB TNS entry>';

           

          For testing the linked server in oracle, use this command –

                  Select * from b_role@<Linked server name>

           

          I hope that this helps. Please keep me posted.

          Regards,

          Dottie Poole

          • 2. Multi-site BDSSA Solution - Oracle DB's
            Bill Robinson

            the blrptadmin 'add site' command should take care of this for oracle.

            • 3. Multi-site BDSSA Solution - Oracle DB's

              Bill,

               

              So I do not need to do anything more than "add site" and "site context"?

              • 4. Re: Multi-site BDSSA Solution - Oracle DB's
                Bill Robinson

                That should be it.  the ‘add site’ should take care of the link – you can check it like:

                 

                Select * from user_db_links;

                 

                As the bsara_dw user after you complete the ‘add site’ and ‘site context’

                • 5. Multi-site BDSSA Solution - Oracle DB's

                  Thanks for the clarifcation. Brandon, sorry for the run-around. I'm going to update the doc to make it clear that nothing further is needed for Oracle (remove the confusing sentence about the linked server procedure needing to be done for both DBs) and I've forwarded Bill's info to dev. Regards, Dottie

                  • 6. Multi-site BDSSA Solution - Oracle DB's
                    Bill Robinson

                    brandon - we've had some internal discussion on this and you may still need to create the db link.  we are verifying.

                    • 7. Multi-site BDSSA Solution - Oracle DB's

                      Okay, I will await your reply.

                       

                      Thank you for all of your help.

                      • 8. Multi-site BDSSA Solution - Oracle DB's

                        Hi Brandon,

                         

                        The development team has confirmed that you do need to create the linked server for Oracle. I've updated the doc with their instructions, but they have not been reviewed yet. Here is what we have so far.

                         

                        First, in the table that describes the add site command, the order in which you perform the tasks should be as follows:

                        1. Create the linked server.
                        2. Run add site to add the site.
                        3. Run set context to set site context.

                         

                        Here is the information for creating a linked server on Oracle.

                         

                        1. Log into SQL Plus as BSARA_DW.
                        2. Run the following command:
                          create databasee link linkedServer connect to blDatabase identified by Password using 'TNS_Entry';
                          where
                          - linkedServer  is the name of the linked server.
                          - blDatabase is the name of the BMC BladeLogic core DB.
                          - Password is the password for logging into the BL DB.
                          - TNS_Entry is the BL DB TNS entry.

                         

                         

                         

                         

                         

                        You can check that this was successful by running the following:
                        select * from b_role@linkedServer

                        I cannot tell you what you should see when you run this command. I'm waiting on that feedback from dev. Hopefully, you know what it should look like. If not, let me know and I'll post as soon as I hear from dev.

                         

                        Regards,

                        Dottie

                        1 of 1 people found this helpful
                        • 9. Multi-site BDSSA Solution - Oracle DB's

                          Just following up on this with a few more questions/concerns...

                           

                          After testing the command given :

                           

                          create databasee link linkedServer connect to blDatabase identified by Password using 'TNS_Entry';

                           

                          I believe there may be an error in this instruction because the proper connect to identified by command would be this:

                           

                          create database link linkedServer connect to DatabaseUser identified by Password using 'TNS_Entry';

                           

                          When I ran your command and tried to test the connection I got "Invalid username/password" and when I tested it with the second command it came back with a different message about tables not existing (which I think means it at least connected)

                           

                          I have a few more questions surrounding the syntax of the add site and site context commands. I see in the documentation where it mentions using the gencred utility to generate a certificate for authenticating. However, we are not using any type of SSO and are instead relying on SRP for authentication. The documentation mentions including the credentials in the command but does not specify the syntax.

                           

                          Can you please inform me what I can use for the credentials without relying on SSO while I run the add site and set context commands.


                          Actually, a full breakdown of the syntax I would need for those commands would be very helpful.

                           

                          Thank you.

                          • 10. Re: Multi-site BDSSA Solution - Oracle DB's
                            Bill Robinson

                            I believe that even if you are using SRP you are still using the BLSSO service for authentication.

                            • 11. Re: Multi-site BDSSA Solution - Oracle DB's

                              I figured this out after some digging in the blrptadmin scripts.

                               

                              For anyone who wants to do this in the future, the order and syntax this goes are like this: Note: Authentication type 1 is SRP

                               

                              sqlplus BSARA_DW/password@TNSentryforDataWarehouse

                               

                              create database link LinkName connect to SecondaryCoreDBUsername identified by SecondaryCoreDBpassword using 'SecondaryCoreDBTNSEntry';

                               

                              cd BDSSAInstallationDirectory/Reports/bin

                               

                              nsh blrptadmin.nsh

                               

                              blrptadmin> add site

                               

                              Accept argument for command:Authenticationparameters (y/n):y

                              Enter GlobalReportAdmin Site: PrimarySiteName

                              Enter GlobalReportAdmin User Name: BLAdmin

                              Enter GlobalReportAdmin role name: BLAdmins

                              Enter authentication type: 1

                              Enter password [Mandatory]: BLAdminPassword

                              Enter site name: SecondarySiteName

                              Enter Site Description: SecondarySiteDescription

                               

                              blrptadmin>set context

                               

                              Accept argument for command:Authenticationparameters (y/n):y

                              Enter GlobalReportAdmin Site: PrimarySiteName

                              Enter GlobalReportAdmin User Name: BLAdmin

                              Enter GlobalReportAdmin role name: BLAdmins

                              Enter authentication type: 1

                              Enter password [Mandatory]: BLAdminPassword

                              Enter site name: SecondarySiteName

                              Enter linked server/database link name: LinkName

                              Enter database type (sqlserver | oracle)::oracle

                              Enter database user: SecondaryCoreDBUsername

                              Enter database password [Mandatory]: SecondaryCoreDBPassword

                              Accept argument for command:JDBC ConnectionParams For ORACLE JDBC Url (y/n):n

                              Accept argument for command:JDBC ConnectionParams For ORACLE (y/n):y

                              Enter database host: SecondaryCoreDBHostname

                              Enter database port: SecondaryCoreDBPortNumber

                              Enter SID: SecondaryCoreDBSID

                               

                              blrptadmin>show site

                               

                              Accept argument for command:Authenticationparameters (y/n):y

                              Enter GlobalReportAdmin Site: PrimarySiteName

                              Enter GlobalReportAdmin User Name: BLAdmin

                              Enter GlobalReportAdmin role name: BLAdmins

                              Enter authentication type: 1

                              Enter password [Mandatory]: BLAdminPassword

                              Enter site name: SecondarySiteName

                               

                              Site Details:

                              Site: SecondarySiteName

                              Id: SecondarySiteID

                              Description: SecondarySiteDescription

                              Context Details:

                              Database Link Name: LinkName

                              Database Username: SecondaryCoreDBUsername

                              Database Connection String: jdbc:oracle:thin:@ SecondaryCoreDBHostname: SecondaryCoreDBPort: SecondaryCoreDBSID

                               


                              Hopefully that will help someone in the future.

                               

                              Thanks for all of your help.