CREATE DATABASE MANUALLY IN ORACLE 10g -STEP BY STEP

Step by step create database manually  in oracle 10g

bash-3.2$ echo $ORACLE_HOME

/opt/oracle/db01/app/oracle/product/10.2.0

bash-3.2$

bash-3.2$

bash-3.2$ echo $ORACLE_BASE

/opt/oracle/db01/app/oracle

 //Create following directory

bash-3.2$cd /opt/oracle/db01/app/oracle/admin

bash-3.2$  mkdir adump 

bash-3.2$  mkdir  bdump 

bash-3.2$  mkdir  cdump 

bash-3.2$  mkdir  dpdump 

bash-3.2$  mkdir  pfile  

bash-3.2$  mkdir udump

//Create following directory

bash-3.2$cd /opt/oracle/db01/app/oracle/oradata/

bash-3.2$ mkdir test

//In Pfile change create init.ora file as per requirmnet

##############################################################################

# Copyright (c) 1991, 2001, 2002 by Oracle Corporation

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192

db_file_multiblock_read_count=16

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Database Identification

###########################################

db_domain=""

db_name=test

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/opt/oracle/db01/app/oracle/admin/test/bdump

core_dump_dest=/opt/oracle/db01/app/oracle/admin/test/cdump

user_dump_dest=/opt/oracle/db01/app/oracle/admin/test/udump

###########################################

# File Configuration

###########################################

control_files=("/opt/oracle/db01/app/oracle/oradata/test/control01.ctl", "/opt/oracle/db01/app/oracle/oradata/test/control02.ctl", "/opt/oracle/db01/app/oracle/oradata/test/control03.ctl")

###########################################

# Job Queues

###########################################

job_queue_processes=10

###########################################

# Miscellaneous

###########################################

compatible=10.2.0.1.0

###########################################

# Processes and Sessions

###########################################

processes=300

sessions=335

###########################################

# SGA Memory

###########################################

sga_target=3812622336

###########################################

# Security and Auditing

###########################################

audit_file_dest=/opt/oracle/db01/app/oracle/admin/test/adump

remote_login_passwordfile=EXCLUSIVE

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=1270874112

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_management=AUTO

undo_tablespace=UNDOTBS1

bash-3.2$ export ORACLE_SID=test

bash-3.2$ sqlplus "/as sysdba"

SQL>startup nomount pfile='/opt/oracle/db01/app/oracle/admin/test/pfile/init.ora';

SQL> create spfile from pfile='/opt/oracle/db01/app/oracle/admin/test/pfile/init.ora';

////////////execute database  script

SQL>create database test

logfile group 1 ('/opt/oracle/db01/app/oracle/oradata/test/redo1.log') size 100M,

            group 2 ('/opt/oracle/db01/app/oracle/oradata/test/redo2.log') size 100M,

            group 3 ('/opt/oracle/db01/app/oracle/oradata/test/redo3.log') size 100M

character set WE8ISO8859P1

national character set utf8

datafile '/opt/oracle/db01/app/oracle/oradata/test/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local

sysaux datafile '/opt/oracle/db01/app/oracle/oradata/test/sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited

undo tablespace undotbs1 datafile '/opt/oracle/db01/app/oracle/oradata/test/undotbs1.dbf' size 100M

default temporary tablespace temp tempfile '/opt/oracle/db01/app/oracle/oradata/test/temp01.dbf' size 100M;

//for creating users
bash-3.2$ sqlplus "/as sysdba"

SQL>alter user sys identified by test;
SQL>alter user system identified by manager;

bash-3.2$ connect /as sysdba

SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

bash-3.2$ connect system/manager

SQL>@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/pupbld.sql

SQL>@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/helpus.sql

CATALOG.SQL: creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.It is located in ORACLE_HOME/rdbms/admin/catalog.sql.

CATPROC.SQL: Runs all scripts required for or used with PL/SQL.It is loacted in ORACLE_HOME/rdbms/admin/catproc.sql.

VI EDITOR COMMANDS

General Startup
 To use vi: vi filename
 To exit vi and save changes: ZZ   or  :wq
 To exit vi without saving changes: :q!
 To enter vi command mode: [esc]


Counts
        A number preceding any vi command tells vi to repeat
 that command that many times.




Cursor Movement

 h       move left (backspace)

 j       move down

 k       move up

 l       move right (spacebar)

 [return]   move to the beginning of the next line

 $       last column on the current line

 0       move cursor to the first column on the 
  current line

 ^       move cursor to first nonblank column on the
  current line

 w       move to the beginning of the next word or 
  punctuation mark

 W       move past the next space

 b       move to the beginning of the previous word 
  or punctuation mark

 B       move to the beginning of the previous word,
  ignores punctuation

        e       end of next word or punctuation mark

        E       end of next word, ignoring punctuation

        H       move cursor to the top of the screen 

        M       move cursor to the middle of the screen

        L       move cursor to the bottom of the screen 




Screen Movement

       G        move to the last line in the file

       xG       move to line x

       z+       move current line to top of screen

       z        move current line to the middle of screen

       z-       move current line to the bottom of screen

       ^F       move forward one screen

       ^B       move backward one line

       ^D       move forward one half screen

       ^U       move backward one half screen

       ^R       redraw screen 
  ( does not work with VT100 type terminals )

       ^L       redraw screen 
  ( does not work with Televideo terminals )




Inserting

       r        replace character under cursor with next 
  character typed

       R        keep replacing character until [esc] is hit

       i        insert before cursor

       a        append after cursor

       A        append at end of line

       O        open line above cursor and enter append mode




Deleting

 x       delete character under cursor

 dd      delete line under cursor

        dw      delete word under cursor

        db      delete word before cursor




Copying Code

        yy      (yank)'copies' line which may then be put by
  the p(put) command. Precede with a count for
  multiple lines.




Put Command
        brings back previous deletion or yank of lines,
 words, or characters

        P       bring back before cursor

        p       bring back after cursor



 Find Commands

 ?       finds a word going backwards

 /       finds a word going forwards

        f       finds a character on the line under the
  cursor going forward

        F       finds a character on the line under the
  cursor going backwards

        t       find a character on the current line going
  forward and stop one character before it

 T       find a character on the current line going
  backward and stop one character before it

 ; repeat last f, F, t, T




Miscellaneous Commands

 . repeat last command

 u undoes last command issued

 U undoes all commands on one line

 xp deletes first character and inserts after
  second (swap)

 J join current line with the next line

 ^G display current line number

 % if at one parenthesis, will jump to its mate

 mx mark current line with character x

 'x find line marked with character x

 NOTE: Marks are internal and not written to the file.




Line Editor Mode
 Any commands form the line editor ex can be issued 
 upon entering line mode.

 To enter: type ':'

 To exit: press[return] or [esc]




ex Commands
 For a complete list consult the 
 UNIX Programmer's Manual




READING FILES
 copies (reads) filename after cursor in file 
 currently editing

 :r filename




WRITE FILE

 :w  saves the current file without quitting




MOVING

 :# move to line #

 :$ move to last line of file




SHELL ESCAPE
 executes 'cmd' as a shell command.

 :!'cmd'

HOW TO CREATE RMAN RECOVERY CATALOG DATABSE

The catalog database is usually a small database it contains and maintains the metadata of all rman backups performed using the catalog.
Use multiple database with same recovery catalog

/////////Create database using DBCA for recovery catalog with name as example rman....

/////Create tablespace for recovery catalog 

SQL>  create tablespace rman_catalog

           datafile '/opt/oracle/oradata/rman/rman01.dbf'

           size 10M autoextend on

           extent management local uniform size 1M

////Create user to maintain recovery catalog

 SQL> create user rman identified by rman

            default tablespace rman_catalog

            quota unlimited on rman_catalog;

///To give roles,privilige to rman user.

SQL>grant connect, resource,recovery_catalog_owner to rman;

///Connect with target database and recovery catalog 

Target database=orcl

Recovery Catalog database=rman

$ rman target sys/orcl@orcl catalog rman/rman@rman

RMAN> create catalog tablespace rman_catalog;

////Register the database to use with recovery catalog

RMAN> register database;

Verify and check using rman command 

RMAN> report schema;

report schema,list backup  etc........... on rman prompt.

Another way on recovery catalog database 

$ export ORACLE_SID=rman

SQL>conn rman/rman

SQL> select * from rc_database;

Here show database information target database with dbid