Thursday, September 02, 2010

ORACLE ERROR: ORA-01031: INSUFFICIENT PRIVILEGES

ORA-01031: INSUFFICIENT PRIVILEGES (ORACLE  LINUX)

When tried to sarted oracle, I got the following error:

ERROR: ORA-01031: insufficient privileges

Following were the steps that I used to start the database instance:

1) Start sqlplus with nolog

# > sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 12 14:40:02 2010
(c) 1982, 2005, Oracle. All rights reserved.
SQL>

2) Connect to oracle as sysdba

SQL> connect sys/password as sysdba;

ERROR:
ORA-01031: insufficient privileges

I hecked around and it turned out the problem was the parameter setting in the sqlnet.ora file.
In my case, the sqlnet.ora file (stored in the $ORACLE_HOME/network/admin directory) contained the following line:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

I changed it to:

SQLNET.AUTHENTICATION_SERVICES= (ALL)

And, I was able to connect to oracle using "connect sys/password as sysdba" and stated the database instance and open the database.

This pointed me to look at the SQLNET.AUTHENTICATION_SERVICES again.

The parameter can set to :

• NONE : cannot connect to database without a password as sysdba (sqlplus /as sysdba)
• NTS: set for using windows NT native authentication
• ALL: ALL for all authentication methods

Authentication Methods Available with Oracle Advanced Security:

• kerberos5 for Kerberos authentication
• cybersafe for Cybersafe authentication

• radius for RADIUS authentication
• dcegssapi for DCE GSSAPI authentication

If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

References:


LINUX SED: APPEND, INSERT AND WRITE TO A FILE FROM A FILE USING SED

APPEND, INSERT AND WRITE LINES TO A FILE FROM A FILE USING SED

This is the first note on Text Manipulations Using Linux Sed.

This note covers how to using Linux sed command to do simple append, insert, and write lines to an output file using a text file as input. Sed provides lot of commands to perform number of operations with the lines in a file. This comes handy in situations that we want to use a script to update files.

1 Sed Dose Not Change Input file.

"SED" stands for Stream EDitor. Sed reads its input from stdin ("standard input," i.e., the console or pipe), or from files, and writes its results to stdout (“standard output”, i.e., the screen). Therefore, sed doesn't modify any input files. Sed takes any number of user-specified editing operations ("commands") and performed on each line in order on the input data.

2 Sed command syntax and option switches.

Sed commands need to be put inside a pair of sigle quotes ‘’. All text lines that involve append, insert and replace need to be preceded by a “backslash” ( “\”). Make sure that there is no space between the command switch and the “\”. For example, the following command adds a line “add this line after the 3rd line”, to the input file inputfile.txt:

#sed ‘3 a\add this line after the 3rd line’ inputfile.txt 

Following are option switches and commands that use in this note. There is more information on sed can be found in the man page here.

Options:
• -n, --quiet, --silent suppress automatic printing of pattern space
• -e, script, --expression=script add the script to the commands to be executed
• -f, script-file, --file=script-file add the contents of script-file to the commands to be executed
Commands:
• P – Upper case P. Print up to the first embedded newline of the current pattern space.
• p – Lower case p. Print the current pattern space.
• = -- print current line number.
• W filename – Uper case W. Write the first line of the current pattern space to filename.
• w filename – Lower case w. Write the current pattern space to filename.
• a \text – Append text, which has each embedded newline preceded by a backslash.
• i \text – Insert text, which has each embedded newline preceded by a backslash.
• c\ text -- Replace the selected lines with text, which has each embedded newline preceded by a backslash.
• D – Upper case D. Delete up to the first embedded newline in the pattern space. Start next cycle, but skip reading from the input if there is still data in the pattern space.
• d – Delete pattern space. Start next cycle.

To test each of the commands and options, let’s use the following files as an example:

# cat sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

3 Sed Append
3.1 Use Sed ‘a\’ to appends a line after every line with the address

Following is the Syntax to append a line with address:

#sed 'ADDRESS a\
  Line which you want to append' filename
‘ADDRESS’ is a number with represents the line, i.e., 3, represent the 3rd line.

Here are some examples using sed append with address command:

• Example 1. Add a line after the 3rd line of the file.
Add the line “San Francisco, Golden City” after the 3rd line.

#sed ‘3 a\San Francisco, Golden City’ sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
San Francisco, Golden City
Paris, The City of Light
Hong Kong, Pearl of the Orient

• Example 2: Add a line at the end of the file.
Sed also works with regurlar expression. The following command add the line “San Francisco, Golden City” at the end of the file

#sed ‘$,a\San Francisco, Golden City’ sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient
San Francisco, Golden City

Here, the ‘$’ is the regurlar expression for end of file.

3.2 Use Sed ‘a\’ to appends a line after every line that match with a pathern

Following is the Syntax to append a line with a match of a pattern:

#sed '/PATTERN/ a\
Line which you want to append' filename

‘/PATTERN/’ is the pattern that wants match,i.e., /Paris/. Pattern need to be put between two forwardslashes,”/”.
Here is an examples using sed append command with a match of pattern:

• Append a line,” San Francisco, Golden City”, after a match of pattern “Paris”

#sed '/Paris/ a\San Francisco, Golden City' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
San Francisco, Golden City
Hong Kong, Pearl of the Orient

4 Sed Insert
The different between sed append and sed insert is that sed append adds the line after the address or match of pattern while sed insert adds the line before the address or match of the parttern.

4.1 Use Sed ‘i\’ to insert a line before every line with the address
Following is the Syntax to append a line with address:

#sed 'ADDRESS a\
Line which you want to append' filename

‘ADDRESS’ is a number with represents the line, i.e., 3, represent the 3rd line.

Here are some examples using sed insert to insert a line before an address.

• Example 1: Insert a line before the 3rd line of the file.
Insert the line “San Francisco, Golden City” before the 3rd line.

# sed '3 i\San Francisco, Golden City' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
San Francisco, Golden City
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

• Example 2: insert a line at the beginning of the file.
Insert the line “San Francisco, Golden City” at the beginning of the file.

#sed '1 i\San Francisco, Golden City' sed_edit.txt
San Francisco, Golden City
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
Paris, The City of Light
Hong Kong, Pearl of the Orient

Here address “1” represents the first line of the file.

4.2 Use Sed ‘i\’ to insert a line before every line that match with a pathern

Following is the Syntax to append a line with a match of a pattern:

#sed '/PATTERN/ i\
Line which you want to append' filename

Here is an example using sed insert to insert a line to a file.
• Insert a line,” San Francisco, Golden City”, before each match of pattern “Paris”

#sed '/Paris/ i\San Francisco, Golden City' sed_edit.txt
New York, Big Apple
Washington DC, The Capital
New Jersey, Garden Stata
San Francisco, Golden City
Paris, The City of Light
Hong Kong, Pearl of the Orient


5 Sed Write To a File
 Following is the Syntax to write to a file with address and pathern:

#sed 'ADDERSSw outputfilename' inputfilename

#sed '/PATTERN/w outputfilename' inputfilename
To store output of the above example, we can do the following:

#sed -n '/Washington/,/Paris/w sed_edit.out' sed_edit.txt

 Sed_edit.out will contain the output of the sed command.

However, to save a copy of the file that after insert,delete or update(replace) you may find it easier just to use the “>outputfilename” syntax.

For example, to save the changes make to the input file with the replace command, we can do:

#sed '/New Jersey, Garden Stata/ c\New Jersey, Garden State' sed_edit.txt > sed_edit.out

References:


Saturday, August 07, 2010

DATA WAREHOUSE CONCEPTS

Data Warehouse Concepts

1. Introduction:

Over the years, I had done a few data warehouse projects and read a few data warehouse and business intelligence books and white pages. However, I had never really summarized the concepts, terminologies, and techniques that I had learned. Here, I will start to put together the basic concepts, and terminologies that are frequently referred to and mythologies and techniques that are frequently used and talked about.

I am going to organize the topic base on the following data warehouse concepts. In this note, I will provide summarize version of the concepts. In the future note, I will provide more explanation and example of the each concept.

  •   Data warehouse and data mart
  •  Fact and Dimension
  •  Star Schema and Snowflake Schema
  •  Extract, Transform and Load (ETL)
  •  Meta Data
  •  Cube
2. Data Warehouse and Data Mart
2.1 Data Warehouse

A data warehouse is a central repository for all or significant parts of the data that an enterprise's various business systems collect. The goal of the data warehouse is make enterprise data easily accessible for strategic decision making.

There are two well-known authors on data warehousing: Bill Inmon and Ralph Kimball. Following are definitions from each of them:


  • Bill Inmon: A data warehouse is a subject-oriented, integrated, nonvolatile, and time-variant collection of  data in support of management’s decisions. 




    • Subject-oriented - Data that gives information about a particular subject instead of about a company's   on-going operations.
    •  Integrated - Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.
    • Time-variant - All data in the data warehouse is identified with a particular time period.
    •  Non-volatile - Data is stable in a data warehouse. More data is added, but data is never removed. This enables management to gain a consistent picture of the business. 


  • Ralph Kimball: The conglomeration of an organization’s data warehouse staging and presentation areas, where operational data is specifically structured for query and analysis performance and ease-of-use.




  • 2.2 Data Mart
     A data mart is a repository of data gathered from operational data and other sources that is designed to serve a particular community of knowledge workers. In scope, the data may derive from an enterprise-wide database or data warehouse or be more specialized. The emphasis of a data mart is on meeting the specific demands of a particular group of knowledge users in terms of analysis, content, presentation, and ease-of-use. Users of a data mart can expect to have data presented in terms that are familiar.

    2.3 Data Warehouse vs. Data Mart:
    •  A data warehouse tends to be a strategic but somewhat unfinished concept. The design of a data warehouse tends to start from an analysis of what data already exists and how it can be collected in such a way that the data can later be used. A data warehouse is a central aggregation of data (which can be distributed physically); 
    • A data mart tends to be tactical and aimed at meeting an immediate need. The design of a data mart tends to start from an analysis of user needs. A data mart is a data repository that may derive from a data warehouse or not and that emphasizes ease of access and usability for a particular designed purpose. 
    3. Fcts and Dimensions
    3.1 Facts

    A fact table is the central table in a star join schema characterized by a composite key, each of whose elements is a foreign key drawn from a dimension table.
    • Represent a business process, i.e., models the business process as an artifact in the data model 
    • Contain the measurements or metrics or facts of business processes. 
    • Most are additive (sales this month), some are semi-additive (balance as of), some are not additive (unit price). 
    • The level of detail is called the “grain” of the table. 
    • Contain foreign keys for the dimension tables include time dimension. 
    • Reolve many-to-many relationships
    3.2 Dimensions
    • Represent the who, what, where, when and how of a measurement/artifact. 
    • Represent real-world entities not business processes. 
    • Give the context of a measurement (subject). 
    • For example for the Sales fact table, the characteristics of the 'monthly sales number' measurement can be a Location (Where), Time (When), Product Sold (What).  
    • The Dimension Attributes are the various columns in a dimension table. In the Location dimension, the attributes can be Location Code, State, Country, Zip code. Generally the Dimension Attributes are used in report labels, and query constraints such as where Country='USA'. The dimension attributes also contain one or more hierarchical relationships.  
    •  Hierarchical relationships.
    4. Star Schemas and Snowflake Schemas
    4.1 Star Schemas

    A start Schema (Dimensional Model) consists a single fact table of metrics surrounded by multiple descriptive dimension tables. A start schema will consist at less on fact table and a few dimensions tables.

      
    4.2 Snowflake Schema

    A snowflake schema is a set of tables comprised of a single, central fact table surrounded by normalized dimensions. The main different between a star schema and a snowflake schema is that star schema tends to have demoralize dimensions while a snowflake schema prefers normalized dimensions.


    5. Extract, Transform and Load (ETL)
    Extract, Transform and Load (ETL) is the name for a process or a group of processes that transport data from source system into the data warehouse or data mart.
    •  Extract is the fist part of an ETL process. It involves extracting the data from different source systems and different data formats.  
    • Transform refers to Appling business rules and logics to the data that was extracted from the source system before loading them into the data warehouse. Data quality assurance can also be performed here. 
    • Load processes load data, after the transform processes, to the data warehouse and data mart which some time also refer to as targets.  
    6. Meta Data

    Meta data literally means "data about data.". It describes the characteristics of a resource. describes how and when and by whom a particular set of data was collected, and how the data is formatted. Metadata is essential for understanding information stored in data warehouses.
    • A data dictionary is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.
    7. Cube

    A cube contains dimensions, hierarchies, levels, and measures. Each individual point in a cube is referred to as a cell.

    Cubes are OLAP objects consisting of related measures and dimensions that you configure within an Analysis Services database. You can define and configure multiple cubes within a single database and each cube can use some or all of the same dimensions. You can also define a single cube that contains multiple measure groups in the same database rather than defining separate cubes. When you define a cube with multiple measure groups, you need to define how dimensions relate to each measure group and customize, as appropriate, dimension objects within each cube and measure group. When defining a cube, you also define advance cube properties, including calculations, KPIs, actions, partitions and aggregations, perspectives and translations.

    8. Smmary
    The concepts which described above were the basic concepts would start with any data were house project. Understand those concepts provide a starting point in any data warehouse development. Each of the concepts mentioned above represent an important part in the data warehouse development life cycle. These concepts are fundamental to design and implement a data warehouse. Following diagram show each of the concepts tied into in each data warehouse layers.


    References: