Scheduling a Job in Linux (Crontab) !!

Today I am going to tell you guys how you can schedule a job/file/command in Linux. Recently I have created a shell script to table backup of OBIA components. I need to run the script manually once in a week. But now my client wanted to schedule the shell script, so that it will run the file automatically and take the backup.

There are some tools available which can be used to schedule the scripts. But here I am going to tell you, through command prompts how can you do the same. 

"crontab" is is file where we can write down all the commands whatever we want to run on a schedule basis. Each user has their own crontab and commands in any given crontab will be executed as the user who owns the crontab. Though these files are in /var/spool/cron/crontabs, they are not intended to be edited directly. crontab file will be executed by cron program. 

1. crontab -l :  To view the commands into crontab file
2. crontab -e :  To edit the file.
3. crontab -r : To remove the file. 


An example of crontab format with commented fields is as fellows:

# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
    0        2          12             *                *            /u01/BI_Bckup/backup.sh

                      

Examples:

Run /usr/bin/test.sh at 12.59 every day and suppress the output (redirect to null)

     59 12 * * * simon /usr/bin/test.sh

# Run demo.sh everyday at 9pm (21:00)

     0 21 * * * demo.sh 

# Run demo.sh every Tuesday to Saturday at 1 am (01:00)

     0 1 * * 2-7 demo.sh

# Run demo.sh at 07:30, 09:30 13:30 and 15:30

     30 07,09,13,15 * * * demo.sh

OBIEE BISystemUser !!

About BISystemUser:

•  BISystemUser is an internal administrator user created during installation in the Weblogic LDAP store for trusted communication between components. The password is created at random.
•  It owns the Oracle BI Server system processes and is not exposed in any user interface.
•  BISystemUser is used for service-to-service authentication.
• This is not an ordinary user account but rather a set of credentials used to authenticate services within the system to each other.
• The credentials of this account are stored in the Credential Store under the system.user key.
• This role is required by Oracle Business Intelligence system components for inter-component communication.
•  The BISystem Role must exist (with the BIAdministrator role), for Oracle Business Intelligence to function correctly.
•  This user name is entered by the person performing the installation, it can be any desired name, and does not need to be named Administrator.
•   The password entered during installation can be changed later using the administration interface for the identity store provider.
•   An administrative user is a member of the BIAdministrators group and has all rights granted to the Oracle Business Intelligence Administrator user in earlier releases, except impersonation. The administrator user cannot impersonate other users.
• The single administrative user is shared by Oracle Business Intelligence and Oracle WebLogic Server. This user is automatically made a member of the Oracle WebLogic Server default Administrators group after installation. This enables this user to perform all Oracle WebLogic Server administration tasks, including the ability to manage Oracle WebLogic Server embedded directory server.
• This is a highly privileged user whose credentials should be protected from non-administrative users.
•  Using a separate user for secure inter-component communication enables you to change the password for the system administrator account without affecting communication between components.
• The name of this user can be changed or a different user can be created for inter-component communication.
•   Role Name: BISystem
•  Members : BISystemUser.
• Role Permissions: 

               oracle.bi.scheduler.manageJobs

               oracle.bi.server.manageRepositories

               oracle.bi.server.impersonateUser

               oracle.bi.server.queryUserPopulation

• Default Credentials:

                map: oracle.bi.system

                key: system.user

OBIEE Log Level Define

Logging level can be defined for each and every user in the RPD separately. Let’s have a quick look on logging levels:

Log Level	Logging level Information that Is logged
Level 0	No logging
Level 1	Logs the SQL statement issued from the client application Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query
Level 2	Logs everything logged in Level 1 Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application
Level 3	Logs everything logged in Level 2 Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails
Level 4	Logs everything logged in Level 3 Additionally, logs the query execution plan.
Level 5	Logs everything logged in Level 4 Additionally, logs intermediate row counts at various points in the execution plan.

Here, I will show how you can set log level  for a user.

Open the OBIEE RPD in online mode and navigate to Manage -> Identity

Click on action -> Set Online User Filter

Type *, it will show all the user list. Select the user and set the log level.

Save RPD.

OBIEE - Date Expressions

1)      First Day of the Previous Year

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.

2)      First Day of the Current Year

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This calculation returns the first day of the year by deducting one less than the total number of days in the year.

3)      First Day of the Next Year

      TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the      next year.

4)      First Day of the Previous Month

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month
.
     5) First Day of the Current Month

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)


This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.

5)      First Day of the Next Month

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.

6)      First Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.

8) Last Day of the Previous Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.

9) Last Day of Current Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.

10) Last Day of the Next Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
11) Last Day of Previous Year

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.

12) Last Day of Current Year

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.

13) Last Day of the Next Year

TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.

14) Last Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.

15) Number of days between First Day of Year and Last Day of Current Month

 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))

For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

16) First day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY,-6, (TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)))

17) Last day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)

OBIEE 11g - Master-Detail Report Feature !!

One of the new features included in OBIEE 11g is the Master-Detail linking feature. The Master-Detail linking allows you to establish a relationship between two or more views; one view is called the Master and will drive changes in one or more views called detail views.

You can think of a Master-Detail relationship in a similar manner that you would when navigating from one  report to another works, but you do not lose sight of the master view.

So, how do we implement a Master-Detail link?

We start out by defining an analysis that will be used to create the master view. Here we have created a simple analysis and filtered for Year = 2008.

Now we want to configure this analysis so that we can create our master view. We will select the “Per Name Qtr” column as the data driver; so, we open the Column Properties window of this column and move to the Interaction tab.

Here we select the “Send Master-Detail Events” under the Value Primary Interaction. Once you select this option a secondary edit box labeled “Specify channel” will be made available. You can enter any unique identifier in this box. For our example, we have entered “MDS2”.

We now navigate to Results and create the view we want as our Master view. Here we are using a simple tabular view. Notice that our “Per Name Qtr” values do not show the typical hyperlink that you would expect from the time dimension column.

Now we need to create a detailed view that will listen to events from our master view. To do this, we will add an additional graphical view from the same analysis. Our graphical view will look at Revenue by Brand with our Quarter values placed into a view slider.

With our graphical view in edit mode, we need to open the properties window.

One of the properties available to us is the “Listen to Master-Detail Events” checkbox. By checking this box, we can define this view to be a detailed view for the event channel we want. In our case, we will enter the value “MDS2” as our event channel which is the same value we defined on the “Per Name Qtr” column properties which is our data driver on the master view.

We want to validate our Master-Detail report is working as expected. We will do this by placing the compound layout on to a dashboard page. I have edited the compound layout so that my views, master and detail, are side-by-side. When you click on a cell under the “Per Name Qtr”; such as, “2008 Q2” on the tabular master view, the secondary detail view (graph) accepts the Qtr value and the slider automatically moves to the selected value.

OBIEE Log files !!

Today, I will talk about the most important log files which will help you to debug the issues regarding Weblogic Server, Managed Server (BI Server), BI system components.

1.      Weblogic Server:

File Name: AdminServer.log

Location : $OBIEE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs

           

2.      Managed Server:

File Name: bi_server1.log

Location : $OBIEE_HOME/user_projects/domains/bifoundation_domain/servers/ bi_server1/logs

3.      BI System Components:

             BI system component: BI Presentation Services

File Name: console~coreapplication_obips1~1.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1

BI system component: BI Servers

File Name: nqserver.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1

BI system component: BI Cluster Controller

File Name: nqcluster.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIClusterControllerComponent/coreapplication_obiccs1

BI system component: BI Schedulers

File Name: nqscheduler.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1

BI system component: BI JavaHosts

File Name: jh.log

Location : $OBIEE_HOME/ instances/instance1/diagnostics/logs/OracleBIJavaHostComponent/coreapplication_obijh1

Login Issue With Weblogic User !!

Sometime you won’t be able to log in to BI Answer portal using Weblogic user itself. This issue will come because of credential mapping issue for BISystemUser.

In this case we need to reset the password or remapping the credentials of BISystemUser.

To remap the credential of BISystemUser steps are mentioned below:

1.       Login to Weblogic Console portal.

2.       Navigate to: Home >Summary of Security Realms > myrealm >Users and Groups

3.       Select BISystemUser and change the password.

4.       Login to Oracle Enterprise Manager (EM).

5.       Navigate to: Weblogic Domain > right click on bifoundation_domain > Security > Credentials > oracle.bi.system > system.user > Edit .  

6.       change the password. (Give same password as you have given in Weblogic console).

7.       Restart the BI System Components from Enterprise Manager (EM).

Now you should be able to login to BI Answer portal.