ORA-20000: Database is not open for MIGRATE. Shutdown and restart using MIGRATE.

How to solve:
ORA-20000: Database is not open for MIGRATE. Shutdown and restart using MIGRATE.

When using the DBCA to create a new database from the starter database, the following error message may occur:
PLS-00302: component 'VALIDATE_COMPONENTS' must be declared

This error can be ignored. However, the @?/rdbms/admin/catpatch.sql script must be run on the newly created database to upgrade it to 9.2.0.6 .

Example:
506 testserver:/home/oracle>sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Apr 21 13:04:58 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> @?/rdbms/admin/catpatch.sql
DOC> BEGIN CATPATCH.SQL */
DECLARE
*
ERROR at line 1:
ORA-20000: Database is not open for MIGRATE. Shutdown and restart using MIGRATE.
ORA-06512: at line 15

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
507 testserver:/home/oracle>

Solution:
SQL> Shutdown immediate
SQL> startup migrate
SQL> @?/rdbms/admin/catpatch.sql

Create Table

Oracle® Database SQL Language Reference
11g Release 1 (11.1)

Part Number B28286-05

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm

CREATE TABLE USER.TABLE_NAME
(
RECORD_KEY NUMBER NOT NULL,
JOB_NO NUMBER NOT NULL,
USERNAME VARCHAR2(300 BYTE),
USER_ACTION VARCHAR2(100 BYTE),
ACTION_DATE DATE
)
TABLESPACE USER_TBL
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;

Codd's Rule No 12: Data Integrity Cannot be Subverted

• There cannot be other paths into the database that subvert data integrity; in other words, you can't get in the 'back door' and change the data in such a manner ad data integrity is violated
• The DBMS must prevent data from being modified by machine language intervention

  
  

Codd's Rules No 11: Supports Distributed Operations

• Data in a relational database can be stored centrally or distributed
• Data from tables on different servers (distributed queries) and from other relational database (heterogeneous queries) can be join by user
• Data integrity must be maintained regardless of the number of copies of data and where it resides
  

Codd's Rules No 10: Data Integrity Is a Function of the DBMS

• In order to be considered relational, data integrity must be an internal function of the DBMS; not the application program
• Data integrity means the consistency and accuracy of the data in the database (i.e., keeping the garbage out of the database)
• Data integrity got 3 type:

1. entity,
2. domain, and
3. referential

• Within the database, data integrity can be enforced procedurally or declaratively

1. Declarative data integrity involves placing or 'declaring' constraints on column
2. Procedural data integrity is maintained through code (i.e., through stored procedures or triggers)
   

Oracle9i and above: How to reclaim (empty) TEMP tablespace

On the last Saturday, my company peoplesoft Oracle data faced TEMP tablespace used until all free harddisk space finished.

I used below command to solve my problem.

Drop Tempfile Command Method - (Oracle9i and higher)

If you are using Oracle9i or higher, another method exists that allows you to simply drop a tempfile. As with this method, it should be performed during off hours with no users logged on performing work.

The first step is to obtain the name of the tempfile to drop.

For this example, my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp2_01.dbf:

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME BYTES

------------------------------------------------------------------------------------------------------------

TEMP /u02/oradata/TESTDB/temp01.dbf 13,107,200,000

The normal size of my temporary tablespace is 512MB which has always been more than adequate for this environment.

A bad query, however, increased the size of this tablespace (my tempfile) to over 13GB and I would like to reclaim that space.

In the example below, I simply drop and recreate the tempfile:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES;

Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Tablespace altered.


SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME BYTES

--------------------------------------------------------------------------------------------------------

TEMP /u02/oradata/TESTDB/temp01.dbf 536,870,912

Codd's Rules No 09: Logical Data Independence

• Logical independence means the relationships among tables can change without impairing the function of applications and adhoc queries
• The database schema or structure of tables and relationships (logical) can change without having to recreate the database or the applications that use it.
  

Codd's Rules No 08: Physical Data Independence

• Applications that access data in a relational database must be unaffected by changes in the way the data is physically stored, i.e., the physical structure
• Below example:

1. The code in an application that accesses data in a file-based database typically depends on the file format,
   
2. e.g., the code references a 'phone number' field that is 1o characters wide, is preceded by the 'zip code' field, followed by the 'fax number' field...
3. If the layout of the data in the file is changed, the application must also be changed.
4. In contrast, the storage and access methods (physical) used in a relational database can change without affecting the user or application's ability to work with the data.
5. The user still only sees tables (logical structure).

• An application that accesses data in a relational database contains only a basic definition of the data (data type and length); it does not need to know how the data is physically stored or accessed
  

Codd's Rules No 07: Supports Set-Based or Relational Operations

• Rows are treated as sets for data manipulation operations (SELECT, INSERT, UPDATE, DELETE)
• A relational database must support:

1. basic relational algebra operations, such as selection, projection and join
2. set operations, such as union, intersection, division and difference
   

Codd's Rules No 06: Provides Alternatives for Viewing Data

• Views are virtual tables or abstractions of the source tables
• A view is an alternative way of looking at data from one or more tables
• A view definition does not duplicate data
• A view is not a copy of the data in the source tables
• Once created, a view can be manipulated in the same way as a source data
• If you change data in a view, you are changing the underlying data in the source table (although there are limits on how data can be modified from a view)
  

Codd's Rules No 05: A Single Language is Used to Communicate with the DBMS

• There must be a single language that handles all communication with the database management system
• The language must support relational operations with respect to:

1. data modification, i.e., SELECT, INSERT, UPDATE, DELETE
2. data definition, i.e., CREATE, ALTER, DROP
3. administration, i.e., GRANT, REVOKE, DENY, BACKUP, RESTORE

• Structured Query Language (SQL) is the in practice standard for a relational database language
• SQL is a 'non-procedural' or 'declarative' language; it allows users to express what they want from the RDBSMS without specifying the details about where it's located or how to get it
  

Codd's Rules No 04: Database is Self-Describing

• In addition to user data, a relational database contains data about itself
• There are two types of tables in a RDBMS:

1. user tables that contain the 'working' data
2. system tables that contain data about the database structure

• System tables can be accessed in the same manner as user tables
  

Codd's Rules No 03: Nulls are Treated Uniformly As Unknown

• Null must always be interpreted as an unknown value
• 'Unknown' is not the same thing as an empty string ("") or zero
• Comparing a null to any value, including itself, returns NULL
  

Codd's Rules No 02: Data is Logically Accessible

• A relational database does not reference data by physical location; there is not such thing as the 'fifth row in the customers table'
• Each piece of data must be logically accessible by referencing:

1. a tables;
2. a primary or unique key value; and
   
3. a column
   

Codd's Rules No 01: Data is Presented in Tables

• A database is form by a set of related tables
• All data is represented as tables only, there is no other way the data can be viewed
• A table is a logical grouping of related data in tabular form
• Table also known as relation or entity
• Tabular form also known as rows and columns
• Each row describes an item (person, place or thing)
• Each row contains information about a single item in the table
• Row also known as record or tuple
• Each column describes a single characteristic about an item
• Column as known as field or attribute
• Data is atomic; there is no more than one value associated with the intersection of a row and column
• The relationships among tables are logical; there are no physical relationships among tables
  

Learning the hard way on Oracle Database Clustering and Application

Today learning the hard way on Oracle Database clustering and Application.

On last Saturday 0200h++, I perform altering temp tempfile to temp2 tempfile on my PROD database. It was not as what I expected which I had done it on other database before. The alteration is OK but not completed.

No error for the whole day.

Last night 2040h++, I want to alter back from temp2 to temp but faced a big error, the whole Oracle application halt. I forget to check the session on PROD which is still access the temp.

To continue...

E. F. Codd's Rules

1. Data is Presented in Tables
2. Data is logically accessible
3. Nulls are treated uniformly as Unknown
4. Database is self describing
5. A simple language is used to communicate with DBMS
6. Provides alternatives for viewing
7. Support set based or relational operations
8. Physical Data Independence
9. Logical Data Independence
   
10. Data Integrity is a Function of the DBMS
11. Support Distributed Operation
12. Data Integrity cannot subverted
    

Need for RDBSMS

• Logical grouping of related data to maintain data in tables
  
• It is in the form of rows and columns
• Relationships are maintained between tables
• Removed data redundancy
  
• Faster search for a particular information
  

Need for Transaction Processing

A.C.I.D

• Atomicity
• - Either all committed or all rolled back
• Consistency
• - Follows user defined integrity constraints
• Isolation
• - A transaction is invisible to other users until completed
• Durability
• - Once committed the results are permanent and survive future system and crashes

• Finding your data fast
• - Data should be accessible fast enough to cater to the huge number of request every second
  

Network Database

• Very similar to Hierarchical Database
• Only differ is Network database support many-to-many relationship: children can have multiple parents and parents can have multiple children
• This type of database is complicated and difficult to maintain
• Main user is computer programmers rather than real end users to solve real time problems

Hierarchical Database

• Data is organised in tree structure as parent and child
• Support one-to-many relationship - parent can have multiple children but a child can have only one parent
• Because of one-to-many relationship, data is systematically accessible. Parent have pointers to their children
• To get to a low-level table start from the root and work all the way down
• Many-to-many relationship is not supported
• Windows based directory management system is one of Hierarchical database.
  

File Management System

• A flat file is where data is stored
  
• To search for a particular data, the whole file must be read and written back.
• Any data must be appended at the end of the file
• Simultaneous transactions or sudden power shutdown etc can render the data corrupted
• If 2 users access at the same time, either
• - Both inserts are successful
• - One of the inserts is lost
• - Info from both inserts is mixed and data corrupted resulting in the file getting corrupted.