RSS

#ORACLE-1 Steps For Manual Database Creation for Oracle

21 Nov

This is my first post for oracle. I am learning it and here is the very basic work Creation Of Oracle Database. Ohh God So much of Work..!! I always miss at least some of it. 😛

DB2 database creation is way simpler than the oracle one.

So documenting steps I followed for the database creation:

Follow the following steps

1. Prepare the pfile File:-

    a. When an Oracle Instance is started, the characteristics of the Instance are established by parameters          specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or    SPFILE. We commonly refer it as the INIT.ORA file. Specifically  I used the PFILE for my database creation.

    b. In pfile path for the parameters like control_file,db_recovery_file_dest,audit_file_dest,diagnostic_dest etc is     mentioned. During the manual db creation we need to manually create these paths as mentioned in pFile.

2. Setting the environment:-
Set the Oracle instance environment by exporting the ORACLE_SID,ORACLE_HOME etc.
e.g. : export ORACLE_SID=
export ORACLE_HOME=
export LD_LIBRARY_PATH=

3. Starting up the Instance:-
During the instance startup, Oracle searches for a suitable initialization parameter file.
One can use the following command by mentioning the path to PFILE parameter at database startup:
SQL> startup pfile=’/<complete-path>/<pfileName>.ora nomount’;
(NOMOUNT – Starts the instance without mounting the database. )

4. CREATE DATABASE COMMAND
Once the instance startup is successful, we can execute the create database command as follows:

CREATE DATABASE <DBNAME>
LOGFILE GROUP 1 (‘<pathToLogFile>/redo01.log’) SIZE 100M,
GROUP 2 (‘<pathToLogFile>/redo02.log’) SIZE 100M,
.
.
.
MAXLOGFILES <Value>
MAXLOGMEMBERS <Value>
MAXLOGHISTORY <Value>
MAXDATAFILES <Value>
CHARACTER SET <Value>
NATIONAL CHARACTER SET <Value> (National Character set for Oracle 11gis AL16UTF16)
EXTENT MANAGEMENT LOCAL
DATAFILE ‘<pathToDataFile>/filename.dbf’ SIZE 400M REUSE
SYSAUX DATAFILE ‘<pathToDataFile>/sysaux01.dbf’ SIZE 400M REUSE
DEFAULT TABLESPACE <TableSpaceName>
DATAFILE ‘<pathToTableSpace>/filename.dbf’
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE <TempTableSpaceName>
TEMPFILE ‘<PathToTempTablespace>/filename.dbf’
SIZE 20M REUSE
UNDO TABLESPACE <UndoTablespaceName>
DATAFILE ‘<PathToUndoTAblespace>/filename.dbf’
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Once you run this you can see the control files, redo log file, Alert log file, .dbf files and .trc
(Background Trace files & User Trace files) files are created.

5. Once database is created Execute the following commands for creating the catalog objects
a. @$ORACLE_HOME/rdbms/admin/catalog.sql
b. @$ORACLE_HOME/rdbms/admin/catproc.sql
c. @$ORACLE_HOME/rdbms/admin/catqm.sql

Here we need to give the following Input parameters asked by the prompt:
i) Password
ii)XML TableSpaceName
iii)TEMP TableSpaceName
6. TableSpace Creation:
Create additional tablespaces for the database:
Most basic syntax for the tablespace creation is as follows:
create tablespace <TableSpaceName> datafile ‘<pathToTableSpace>/<DataFileName>.dbf’ size 500m            autoextend on;

7. Import the DB Dump using the following impdp command:
a. Put the database dump in the DATA_PUMP_DIR directory. Path for the DATA_PUMP_DIR can be found        out using simple command
SQL> select * from dba_directories;
b. Import the DB dump uising the following impdp command:
impdp userid = <userId-system>/<pwd> DIRECTORY=data_pump_dir dumpfile=<Dump File Name>                 logfile =<LogFileName> remap_schema=<OLD_SCHEMA_NAME>:<NEW_SCHEMA>

And Its Done 🙂

Advertisements
 
Leave a comment

Posted by on November 21, 2013 in Oracle

 

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: