INSTALL AND CONFIGURE ORACLE FAIL SAFE

Here is really nice article how to install and configure windows cluster and oracle fail safe.

MOVING CONTROL FILES

1. Change control file location in spfile

   

2. Shutdown database

   

3. Move fiscally control files to the new location
4. Start database

   

5. Show location of control files

   

MOVE DATAFILES AND REDO LOGS TO NEW LOCATION

1. Query current datafiles location

   

2. Shutdown database

   

3. Move datafiles to the new location

   In my case I move datafiles to L:\oradata

4. Startup mount database

   

5. Alter database and specify the new location of each datafile

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\system01.dbf' to 'l:\oradata\system01.dbf';

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\sysaux01.dbf' to 'l:\oradata\sysaux01.dbf';

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\undotbs01.dbf' to 'l:\oradata\undotbs01.dbf';

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\users01.dbf' to 'l:\oradata\users01.dbf';

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\example01.dbf' to 'l:\oradata\example01.dbf';

   Moving redologs

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\redo01.log' to 'l:\oradata\redo01.log';

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\redo02.log' to 'l:\oradata\redo02.log';

   Alter database rename file 'c:\oracle\product\10.2.0\oradata\mruhtel\redo03.log' to 'l:\oradata\redo03.log';

6. Open database
7. Query datafile and logfile location

   

   

Upgrade oracle to 10.2.0.1 to 10.2.0.4

1. Backup the database
2. Download pathset 6810189 from metalink
3. Shutdown database

   

4. Stop oracle services

   

5. Upgrade the database

   

   

   

   

   

   

   

6. Start all services

   

7. Startup the database with option upgrade

   

   Screen clipping taken: 11.2.2011 г.; 11:12 ч.

8. Run script catupgrd.sql

   

   On the end of upgrade will have following screen

   

9. Check the logfile for any errors and if necessary rerun the script
10. Restart the database

    

11. Compile invalid objects

    

12. Job is now done and we can check the version of the db

    

13. Now I am going to upgrade also oracle on NODE2

SYNONYMS

Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.

You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE.

You can refer to synonyms in the following DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT.

To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege.

To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM system privilege.

To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

You can read the whole article at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm

USING VIEWS

Before we are able to create views, we should know what view actually means. It is logical structure based on one or more tables, or another view.

We are treating views as any other table in the database. We can query them, perform insert, update and delete. Actually when we perform DMLs on a view, the operation will consider the table on which base the view is created.

There are many reason why we should use views. One of them is security. What does it means? It means that views give us opportunity to hide some critical information stored in the table.

Example:

Let take table employees from HR schema. It contains information about all employees in the company, names, emails, phones, salary…. Now we need to give access to the people from payroll department just to columns department_id, first_name, last_name, salary. To do this we have to create a view based on table employees.



Screen clipping taken: 4.2.2011 г.; 13:15 ч.

Now let take we should give the shipping department manager access to the information about all employees in his own department and hide from him the information about the rest of the employees in the company. To do this we are going to create a view called shipping_staff. This view will be based on 2 tables: employees and departments



Other reasons for creating and using views can be:

1. Better performance
2. Simplify SQL
3. Preventing errors...

   In our first example the view is based on just one table and it doesn't use any functions. It make it simple view.

   In our second example the view is based on two tables which make it automatically complex view

SIMPLE VIEW	COMPLEX VIEW
Select from one table	Select from one or more tables
Does not contain functions	Contains functions
Can perform DML trough simple views	Can't always perform DML trough complex views

REMOVING ROWS FROM A TABLE

We can remove rows from a table using different ways. First of them is using DELETE command. It will remove row from a table. Using WHERE clause we specify which rows to be deleted. If we omit WHERE clause all rows from the table will be deleted.

It is important to remember that DELETE command is a DML command and it can be rolled back. It means that if we want to remove rows permanently we need to COMMIT.

Examples:



The DELETE command is not COMMITED yet, so we are able to undo it:



If we want to remove rows permanently, we should COMMIT



We can see that after DELETE command is committed, rollback won't be able to undo the changes.

TRUNCATE TABLE

This command will remove ALL ROWS from a table. It is important to remember that it can not be rolled back.

Example:



If we want to remove whole table from a database ( rows, privileges, indexes), we need to use DROP command.



Rollback will again not undo changes made with DROP command.



Screen clipping taken: 4.2.2011 г.; 11:27 ч.

There is way in Oracle 10g to undo "dropping table" using FLASHBACK.

DELETE

Use the DELETE statement to remove rows from:

• An unpartitioned or partitioned table

• The unpartitioned or partitioned base table of a view

• The unpartitioned or partitioned container table of a writable materialized view

• The unpartitioned or partitioned master table of an updatable materialized view

For you to delete rows from a table, the table must be in your own schema or you must have the DELETE object privilege on the table.

For you to delete rows from an updatable materialized view, the materialized view must be in your own schema or you must have the DELETE object privilege on the materialized view.

For you to delete rows from the base table of a view, the owner of the schema containing the view must have the DELETE object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the DELETE object privilege on the view.

You can read the whole article here.

 

 

UPDATE TABLE

Use the UPDATE statement to change existing values in a table or in the base table of a view or the master table of a materialized view.

For you to update values in a table, the table must be in your own schema or you must have the UPDATE object privilege on the table.

For you to update values in the base table of a view:

• You must have the UPDATE object privilege on the view, and

• Whoever owns the schema containing the view must have the UPDATE object privilege on the base table.

The UPDATE ANY TABLE system privilege also allows you to update values in any table or in the base table of any view.

You can find the whole article at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#SQLRF01708

SET OPERATORS

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character).

If component queries select character data, then the datatype of the return values are determined as follows:

• If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.

• If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.

If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:

• If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE.

• If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.

• If all queries select values of type NUMBER, then the returned values have datatype NUMBER.

In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.

Examples The following query is valid:

SELECT 3 FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;




This is implicitly converted to the following compound query:

SELECT TO_BINARY_FLOAT(3) FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;




The following query returns an error:

SELECT '3' FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;




Restrictions on the Set Operators The set operators are subject to the following restrictions:




• 
  

  The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.

  
  
• 
  

  The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.

  
  
• 
  

  If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause.

  
  
• 
  

  You cannot also specify the for_update_clause with the set operators.

  
  
• 
  

  You cannot specify the order_by_clause in the subquery of these operators.

  
  
• 
  

  You cannot use these operators in SELECT statements containing TABLE collection expressions.




Note:


To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.


UNION Example The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

SELECT location_id, department_name "Department", 
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name 
   FROM warehouses;

LOCATION_ID Department            Warehouse
----------- --------------------- --------------------------
       1400 IT
       1400                       Southlake, Texas
       1500 Shipping
       1500                       San Francisco
       1600                       New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction
...




UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;

SELECT location_id  FROM locations 
UNION ALL 
SELECT location_id  FROM departments;




A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.


INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;




MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

ARTICLE IS TAKEN FROM :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381

CROSS JOINS

Cross joins produce all possible combinations of rows from tables which are joining. If we have again table A and table B, and each table has 3 rows, cross joins will produce 3x3=9 rows.

Examples:

Pure natural join can produce also an cartesian product when the joining tables do not have identical named columns.

Example :

OUTER JOINS

LEFT OUTER JOINS

Let take we have two tables A and B. The left outer joins will return all the rows from the left table A in conjunction with the matching rows from the right table B. In case there are no columns matching in table B, it returns NULL values.

Example:





There will be 122 rows returned (there are some departments without employees to be assigned to so there are entries with NULL values in columns FIRST_NAME, LAST_NAME)

If we just change left and right table the result will be different:



There are 107 rows returned (number of rows present in table EMPLOYEES)



There are 107 rows returned (number of rows present in table EMPLOYEES)

RIGHT OUTER JOINS

I am taking again two tables A and B. The right outer joins will return all the rows from the left table B in conjunction with the matching rows from the right table A. In case there are no columns matching in table A, it returns NULL values. Example:





There will be 122 rows returned

If we change the table places will get different result



`There will be 107 rows returned (number of rows in EMPLOYEES table)

FULL OUTER JOINS

It combines both left outer and the right outer joins together.





There will be 123 rows returned (there are some departments without employees to be assigned to so there are entries with NULL values in columns LAST_NAME, SALARY )

Using Self-Joins to Access Related Records in the Same Table

You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself, such as the employees table shown below:

id first_name last_name manager
----------- --------------- --------------- -----------
1 Pat Crystal NULL
2 Dennis Miller 1
3 Jacob Smith 1

Suppose you're tasked with writing a SQL query to retrieve a list of employees and their managers. You can't write a basic SQL SELECT statement to retrieve this information, as you need to cross reference information contained in other records within the same table. Fortunately, you can use a self-join to solve this dilemma by joining the table to itself.

You can find the whole article at http://databases.about.com/od/sql/a/selfjoins.htm

NATURAL JOIN

NATURAL JOIN CLAUSE

To be able to join two tables using NATURAL JOIN they must have a common column. It compares those common columns with each other and returns the results. It automatically detects the join keys, based on the name of the matching column in both tables. For example in HR schema we can join EMPLOYEES with JOB_HISTORY table using the common columns EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID





The above statement returns only one row as result, because there is just one entry existing in both tables in same time.

Let take now ORDER and CUSTOMERS tables from OE schema. There is only one common column in both tables: customer_id





There is again just one entry presents in both tables in same time.

NATURAL JOIN USING CLAUSE

It allows to join tables on one or more common columns (not on ALL common columns). When we use JOIN USING clause we can specify the join_coulmn(s). In other words we use it when two tables have more than one column with same name but don't to use all of them. It has following syntax:

SELECT table1_column, table2_column…

FROM table1

JOIN table2 USING(join_column1, join_column2…);

Examples:



NATURAL JOIN ON CLAUSE

In some cases we need to join tables without using common column_name. This can be done with JOIN … ON clause.

Example:

It is important to remember that we NEVER can use all three keywords (NATURAL, USING and ON) in one JOIN clause.