Feeds:
Posts
Comments

Archive for September, 2012

When you start up a database, you create an instance of that database and you determine the state of the database.
Normally, you start up an instance by mounting and opening the database. 
Doing so makes the database available for any valid user to connect to and perform 
typical data access operations.

Options for Starting Up a Database

You can start up a database instance with SQL*Plus, Recovery Manager, or Enterprise Manager.

To start an instance,
the database must read instance configuration parameters (the initialization parameters) 
from either a server parameter file (SPFILE) or a text initialization parameter file.

When you issue the SQL*Plus STARTUP command,
the database attempts to read the initialization parameters from an SPFILE in a platform-specific 
default location. If it finds no SPFILE, it searches for a text initialization parameter file.

For UNIX or Linux, the platform-specific default location (directory) for the SPFILE and 
text initialization parameter file is: 
$ORACLE_HOME/dbs 

For Windows NT and Windows 2000 the location is:
%ORACLE_HOME%\database

Starting an Instance, and Mounting and Opening a Database

Normal database operation means that an instance is started and the database is mounted and open. 
This mode allows any valid user to connect to the database and perform data access operations.

Starting an Instance Without Mounting a Database

You can start an instance without mounting a database. 
Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT clause:
STARTUP NOMOUNT 

starting an Instance and Mounting a Database

You can start an instance and mount a database without opening it, 
allowing you to perform specific maintenance operations.
For example, the database must be mounted but not open during the following tasks:

-> Enabling and disabling redo log archiving options.
-> Performing full database recovery. 
The following command starts an instance and mounts the database, but leaves the database closed

STARTUP MOUNT


Restricting Access to an Instance at Startup

You can start an instance, and optionally mount and open a database, 
in restricted mode so that the instance is available only to administrative personnel 
(not general database users).

-> Perform an export or import of data

-> Perform a data load (with SQL*Loader)

-> Temporarily prevent typical users from using data

-> Perform certain migration or upgrade operations

The following command starts an instance (and mounts and opens the database) in restricted mode:
STARTUP RESTRICT


You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.

To Disable the resticted mode:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

Forcing an Instance to Start


When you experience problems when starting an instance.
You can start the instance in force mode.
STARTUP FORCE

If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.
In this case, 
beginning with Oracle Database 10g Release 2, 
the alert log shows the message "Shutting down instance (abort)" followed by "Starting ORACLE instance (normal)."

Starting an Instance, Mounting a Database, and Starting Complete Media Recovery

If you know that media recovery is required, 
you can start an instance, mount a database to the instance, 
and have the recovery process automatically start by using the STARTUP command with the RECOVER clause:
STARTUP OPEN RECOVER


If you attempt to perform recovery when no recovery is required, Oracle Database issues an error message.

Mounting a Database to an Instance

When you need to perform specific administrative operations, 
the database must be started and mounted to an instance, but closed. 
You can achieve this scenario by starting the instance and mounting the database.

ALTER DATABASE MOUNT;

Opening a Closed Database

You can make a mounted but closed database available for general use by opening the database. 
To open a mounted database, use the ALTER DATABASE statement with the OPEN clause
ALTER DATABASE OPEN;

Opening a Database in Read-Only Mode

Opening a database in read-only mode enables you to query an open database while eliminating any 
potential for online data content changes.
While opening a database in read-only mode guarantees that datafile and redo log files are not 
written to, it does not restrict database recovery or operations that change the state of the 
database without generating redo

ALTER DATABASE OPEN READ ONLY;

You can also open a database in read/write mode as follows
ALTER DATABASE OPEN READ WRITE;

However, read/write is the default mode.

You cannot use the RESETLOGS clause with a READ ONLY clause.

Shutting Down with the NORMAL Clause

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:
SHUTDOWN NORMAL


The NORMAL clause is optional, because this is the default shutdown method if no clause is provided.

Normal database shutdown proceeds with the following conditions:

-> No new connections are allowed after the statement is issued.

-> Before the database is shut down, the database waits for all currently connected users to disconnect from the database.

-> The next startup of the database will not require any instance recovery procedures.

Shutting Down with the IMMEDIATE Clause


To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE clause:
SHUTDOWN IMMEDIATE


Immediate database shutdown proceeds with the following conditions:

-> No new connections are allowed. 
-> Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, 
this method of shutdown might not complete quickly, despite its name.)

-> Oracle Database does not wait for users currently connected to the database to disconnect. 
The next startup of the database will not require any instance recovery procedures.

Shutting Down with the TRANSACTIONAL Clause

When you want to perform a planned shutdown of an instance while allowing active transactions 
to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:
SHUTDOWN TRANSACTIONAL


Transactional database shutdown proceeds with the following conditions:

-> No new connections are allowed, nor are new transactions allowed to be started, 
after the statement is issued.

-> After all transactions have completed, any client still connected to the instance is disconnected.

The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, 
does not require all users to log off.


Shutting Down with the ABORT Clause

You can shut down a database instantaneously by aborting the database instance. 
If possible, perform this type of shutdown only in the following situations:

--> The database or one of its applications is functioning irregularly and none of the other 
types of shutdown works.

--> You need to shut down the database instantaneously (for example, if you know a power shutdown 
is going to occur in one minute).

--> You experience problems when starting a database instance.

When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause:
SHUTDOWN ABORT


An aborted database shutdown proceeds with the following conditions:

-> No new connections are allowed, nor are new transactions allowed to be started, 
after the statement is issued.

-> Current client SQL statements being processed by Oracle Database are immediately terminated.

-> Uncommitted transactions are not rolled back.

-> Oracle Database does not wait for users currently connected to the database to disconnect. 
The database implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

That it...

Hope its Helpful..
Nimai Karmakar
Advertisements

Read Full Post »

Mannual Database Creation in oracle 10g

 

Yesterday I got a problem, unfortunately i got my database deleted(not accidently)…

Actually I was trying to do something that what happen if i delete my database and make it manually.Cause now a days DBA

optionally choose to do all there work through command mode.so i choose to delete my database and build it again in step by step.

 

 

The software is already intsalled and listener is created for you database with proper name..........


There can be more than one Oracle instance on a single machine. In order to be able to distinguish these instances, Oracle uses a SID (System Identifier) which is a string. 
The SID can be set through the ORACLE_SID environment variable.
There can be more than one Oracle instance on a single machine. In order to be able to distinguish these instances, Oracle uses a SID (System Identifier) which is a string. 
The SID can be set through the ORACLE_SID environment variable.
Export ORACLE_SID=PRODDB

Create the necessary directories

ORACLE_BASE-   /apps/oracledb
/apps/oracledb/flash_recovery_area/PRODDB/controlfile
/apps/oracledb/flash_recovery_area/PRODDB/onlinelog
/apps/oracledb/oradata/PRODDB/controlfile
/apps/oracledb/oradata/PRODDB/datafile
/apps/oracledb/oradata/PRODDB/onlinelog




Create initial initialization file
Oracle states that it has greatly reduced the number of necessary initialization parameters in Oracle 10g. 
This seems to be true. To create the database, I used that minimalistic file as $ORACLE_HOME/dbs/initPRODDB.ora:

PRODDB.__db_cache_size=427819008
PRODDB.__java_pool_size=4194304
PRODDB.__large_pool_size=4194304
PRODDB.__shared_pool_size=159383552
PRODDB.__streams_pool_size=0
*.audit_file_dest='/apps/oracledb/admin/PRODDB/adump'
*.background_dump_dest='/apps/oracledb/admin/PRODDB/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/apps/oracledb/oradata/PRODDB/controlfile/PRODDBcontrol1.ctl','/apps/oracledb/flash_recovery_area/PRODDB/controlfile/PRODDBcontrol2.ctl'
*.instance_name='PRODDB'
*.core_dump_dest='/apps/oracledb/admin/PRODDB/cdump'
*.db_block_size=8192
*.db_create_file_dest='/apps/oracledb/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PRODDB'
*.db_recovery_file_dest='/apps/oracledb/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODDBXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=601882624
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/apps/oracledb/admin/PRODDB/udump'


Add entry to oratab
This is something which is quite often forgotten. 
It can help you to quickly navigate between Oracle homes you installed on your machine.
 Simply have oraenv set the necessary parameters for you. 
To add your new database, PRODDB  to your oratab,
 edit /etc/oratab or /var/opt/oracle/oratab and add the last line (the one above specifies an Oracle Home): 


# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/apps/oracledb/product/10.2.0:N
PRODDB:/apps/oracledb/product/10.2.0:N

Start the creation
Now it's about time to start. You create the database using the create database command. 




CREATE DATABASE PRODDB
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
DATAFILE 
'/apps/oracledb/oradata/PRODDB/datafile/system.dbf'
SIZE 300M AUTOEXTEND ON NEXT  10240K MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 
'/apps/oracledb/oradata/PRODDB/datafile/sysaux.dbf'
SIZE 120M AUTOEXTEND ON NEXT  10240K MAXSIZE 1024M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 
'/apps/oracledb/oradata/PRODDB/datafile/temp.tmp'
SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS1 DATAFILE
'/apps/oracledb/oradata/PRODDB/datafile/undo.dbf'
SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 (
    '/apps/oracledb/oradata/PRODDB/onlinelog/grouponeone.log',
    '/apps/oracledb/flash_recovery_area/PRODDB/onlinelog/grouponetwo.log'
  ) SIZE 50M,
  GROUP 2 (
    '/apps/oracledb/oradata/PRODDB/onlinelog/grouptwoone.log',
    '/apps/oracledb/flash_recovery_area/PRODDB/onlinelog/grouptwotwo.log'
  ) SIZE 50M,
  GROUP 3 (
    '/apps/oracledb/oradata/PRODDB/onlinelog/groupthreeone.log',
    '/apps/oracledb/flash_recovery_area/PRODDB/onlinelog/groupthreetwo.log'
  ) SIZE 50M
USER SYS IDENTIFIED BY sys123
USER system IDENTIFIED BY sys123
/




Edit the code as per your requirement and run it on sql editor or save it name.sql and run the 
sql scipt from console mode.



-------------If you get this error------
CREATE DATABASE PRODDB
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file:
'/apps/oracledb/oradata/PRODDB/controlfile/PRODDBcontrol1.ctl'
ORA-27037: unable to obtain file status
Linux Error: 13: Permission denied
Additional information: 1

-----------Solution is ---------

give proper permission to each folder u create 
---------------------------------------------------------


we are done.......
run the following scripts to make data dictionary views,tables and all..


@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catproc.sql;

catalog.sql creates the data dictionary. catproc.sql creates all structures required for PL/SQL.
catalog.sql calls, for example, catexp.sql which is a requirement for exp, or dbmsstdx.sql which is a requirement to create triggers.
connect system/password
@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/pupbld.sql
@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/helpus.sql


thats it...

hope it's helpful to you....
Thanks & Regards
Nimai Karmakar

Read Full Post »

Daniel Westermann's Blog

simplicity...that's it

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have