Creating an Oracle 10g database manually on ASM

Just for the understading of junior members in my present team..!!

Before Proceeding with Manual Database Creation Make sure you have the asm instance is running, Diskgroup is mounted and Sufficient space available for creating the database.

/home/oracle $ ps -ef | grep +ASM
oracle 25792 1 0 May25 ? 00:00:00 asm_pmon_+ASM

SQL> select group_number,name,state,total_mb/1024 TOTAL_GB,free_mb FREE_GB from v$asm_Diskgroup;

GROUP_NUMBER NAME STATE TOTAL_GB FREE_GB
------------ ------------------------------ ----------- ---------- ----------
1 DATA MOUNTED 4 2.5

Specifying the Instance's SID

The SID can be set through the ORACLE_SID environment variable.

export ORACLE_SID=ORCL

Creating the initialization parameter file

*.audit_file_dest='/u01/oracle/product/10.2.0/db_1/admin/orcl/adump'
*.background_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA
*.core_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/orcl/udump'
db_create_file_dest='+DATA'
log_archive_dest_1='LOCATION=+DATA'
db_create_online_log_dest_1='+DATA'

Note : Make sure you create the dump file directories( adump,udump,bdump,cdump)

Create Password File

Make sure you create the password file before you proceed with the instance startup.

Starting the instance

SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 24 16:05:15 2008

 Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect sys/password as sysdba

Connected to an idle instance.

SQL*Plus tells us that we're connected to an idle instance. That means that it is not yet started. So, let's start the instance. We have to start the instance without mounting (nomount) as there is no database we could mount at the moment.

SQL> startup nomount pfile='/home/oracle/pfile.ora'

ORACLE instance started.
Total System Global Area  113246208 bytes
Fixed Size                   787708 bytes
Variable Size              61864708 bytes
Database Buffers           50331648 bytes
Redo Buffers                 262144 bytes

This created the SGA (System Global Area) and the background processes.

Creating the database

SQL>create database orcl
logfile group 1 ('+DATA') size 10M,
group 2 ('+DATA') size 10M,
group 3 ('+DATA') size 10M
character set WE8ISO8859P1
national character set utf8
datafile '+DATA' size 50M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile '+DATA ' size 10M autoextend on next 10M maxsize unlimited
undo tablespace undo datafile '+DATA' size 10M
default temporary tablespace temp
tempfile '+DATA' size 10M;

If something goes wrong with the creation, Oracle will write an error into the alert log.

Completing the DB creation

In order to complete the db creation, the following scripts must be run as sys

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

SQL> connect system/manager
SQL> @?/sqlplus/admin/pupbld

After Creating set your asm environment and you can see all these files from asmcmd.

ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ASMCMD> pwd
+DATA/ORCL
ASMCMD>


Suggestions and Corrections are welcome..!!!