Feeds:
Posts
Comments

Archive for the ‘Database creation’ Category

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

Advertisements

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