Manual Installation of Oracle Database on (ASM)

Configure raw devices to create ASM see ASM Configuration
NOTE: I am assuming that you have installed ORACLE software already. If not then install Oracle Software. We will see how to create Oracle Database on ASM Manually (without GUI).

Check the raw disk:

# ls /dev/raw/*

Check the Ownership and permission of Raw devices:

# ls -ltr /dev/raw/raw*
crwxrwxrwx 1 oracle dba 162, 1 Oct 28 19:38 /dev/raw/raw1
crwxrwxrwx 1 oracle dba 162, 2 Oct 28 19:38 /dev/raw/raw2
crwxrwxrwx 1 oracle dba 162, 3 Oct 28 19:38 /dev/raw/raw3
crwxrwxrwx 1 oracle dba 162, 4 Oct 28 19:38 /dev/raw/raw4

Starting the css service:

Go to ORACLE_HOME/bin directory and execute the following script as a ROOT user.
# cd /oradata/oracle/product/11.1.0.7/db_1/bin
# ./localconfig add

Creating the parameter pfile for asm instance:

$ cd $ORACLE_HOME/dbs
$ vi init+asm1.ora
And paste the following parameters. You can change it as per your specifications.

instance_type=asm
instance_name=+asm1
asm_diskstring='/dev/raw/raw*'
shared_pool_size=64m
asm_power_limit=2
asm_diskgroups='DG1'

Starting the ASM instance and creating the diskgroup:

$ export ORACLE_SID=+asm
$ sqlplus "/ as sysdba"
Sql> startup nomount
Sql> select name,path from v$asm_disk;Note: I dont need mirroring of disks so I am creating diskgroup with "external redundancy" option for utilizing all rawdevices.

Creating of the diskgroup:

Sql> create diskgroup dg1 external redundancy disk                  '/dev/raw/raw1’,’/dev/raw/raw2','/dev/raw/raw3','/dev/raw/raw4';

Check the name and status of Diskgroup:

Sql> select name,state from v$asm_diskgroup;
NAME       STATE
------------------------------ -----------
DG1        MOUNTED
Diskgroup is created now its time to create database.
For creating database manually follow the steps:

Step 1:Create Directory structure with Oracle ownership and permission as below:

# cd /oradata
# mkdir /oradata/dev
# mkdir adump diag flash_recovery_area
# chmod -R 777 /oradata/dev
# chown -R oracle:dba /oradata/dev

Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

$ cd $ORACLE_HOME/dbs
$ vi initdev.oraAnd paste the following parameters. You can change it as per your specifications.

db_name='dev' #you can give whatever db_name you want
db_block_size=8192
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
control_files= '+DG1/dev/control/control01.ctl','+DG1/dev/control/control02.ctl', '+DG1/dev/control/control03.ctl'

compatible='11.1.0'
diagnostic_dest='/oradata/oracle/product/11.1.0.7/dign'
log_buffer=10485760
log_checkpoints_to_alert=TRUE
max_dump_file_size='20480'
shared_pool_reserved_size=40M
shared_pool_size=400M
timed_statistics=true
db_block_buffers=100# SMALL
db_file_multiblock_read_count=8# SMALL
db_files=80# SMALL
global_names=TRUE
log_checkpoint_interval=10000
parallel_max_servers=5# SMALL
processes=50# SMALL
undo_management='AUTO'
undo_tablespace='UNDOTBS1'

Step 3:Prepare Create Database script:

$ vi createdb.sql

CREATE DATABASE dev
USER sys IDENTIFIED BY sys
USER system IDENTIFIED BY system
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 8
MAXLOGHISTORY 500
LOGFILE 

GOUP 1 (
'+dg1/dev/redologs/redo01_1.log',
'+dg1/dev/redologs/redo01_2.log'
) SIZE 10M,
GROUP 2 (
'+dg1/dev/redologs/redo02_1.log',
'+dg1/dev/redologs/redo02_2.log'
) SIZE 10M,
GROUP 3 (
'+dg1/dev/redologs/redo03_1.log',
'+dg1/dev/redologs/redo03_2.log'
) SIZE 10M
DATAFILE
'+dg1/dev/datafiles/system01.dbf' SIZE 100M
SYSAUX DATAFILE '+dg1/dev/datafiles/sysaux01.dbf' SIZE 50M
UNDO TABLESPACE UNDOTBS1 DATAFILE '+dg1/dev/datafiles/undotbs01.dbf' SIZE 100M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+dg1/dev/datafiles/temp01.dbf' SIZE 40M
CHARACTER SET UTF8;

Step 4: Check ASM instance is Up or not if not then start the ASM instance:

$ ps -ef | grep AMS
oracle 30827 1 0 19:57 ? 00:00:00 asm_pmon_+ASM
oracle 30829 1 0 19:57 ? 00:00:00 asm_vktm_+ASM
oracle 30833 1 0 19:57 ? 00:00:00 asm_diag_+ASM
oracle 30835 1 0 19:57 ? 00:00:00 asm_psp0_+ASM
oracle 30839 1 0 19:57 ? 00:00:00 asm_dia0_+ASM
oracle 30841 1 0 19:57 ? 00:00:00 asm_mman_+ASM
oracle 30843 1 0 19:57 ? 00:00:00 asm_dbw0_+ASM
oracle 30845 1 0 19:57 ? 00:00:00 asm_lgwr_+ASM
oracle 30847 1 0 19:57 ? 00:00:00 asm_ckpt_+ASM
oracle 30849 1 0 19:57 ? 00:00:00 asm_smon_+ASM
oracle 30851 1 0 19:57 ? 00:00:01 asm_rbal_+ASM
oracle 30853 1 0 19:57 ? 00:00:00 asm_gmon_+ASM
oracle 31686 31443 0 22:04 pts/1 00:00:00 grep ASM

Step 5:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

Go to Oracle users Home directory
$ cd /home/oracle
Edit the .bash_profile file

$ vi .bash_profile
export ORACLE_HOME=/oradata/oracle/product/11.1.0.7/db_1
export ORACLE_SID=dev
PATH=$PATH:$ORACLE_HOME/bin

Save it and run the bash_profile
$ . .bash_profile
This will set the Oracle Environment variables in Unix-based operating system.

Step 6: Start up database in nomount stage

$ sqlplus " / as sysdba"
SQL> startup nomount
ORACLE instance started.
Total System Global Area 464556032 bytes
Fixed Size 1300492 bytes
Variable Size 444598260 bytes
Database Buffers 4194304 bytes
Redo Buffers 14462976 bytes
SQL>

Step 7:Execute Create Database script created in Step 3

SQL>@/oradata/createdb.sql
Database created

Step 8:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:

a) catalog.sql =>Creates dictionary tables and views
b) catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c) pupbld.sql =>Creates user profiles.
You will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and pupbld.sql
in $ORACLE_HOME/sqlplus/admin path.

Tablespace Management

By default each database will have five tablespaces in oracle 10g.

1. System : Tablespace-contains data dictionary of database
2. Sysaux : Contains database statistics
3. Undo : Contains Pre Image data
4. Temporary : Temporary operations are performed in Temporary Tablespace if PGA is not enough
5. Users : Default tablespace for all DB users / Application schemas

Creating Tablespace:-

Syntax:
SQL> create tablespace tablespace_name
datafile 'location of datafile/datafile_name.dbf' size 50m;

Example:
SQL> create tablespace test01
datafile '/oradata/oracle/db/test01.dbf' size 50M;

To check Tablespace information:

SQL> select tablespace_name from dba_tablespaces;

Adding the Space to Tablespace (Adding Datafile) :-

Syntax:
SQL> alter tablespace tablespace_name add datafile
'datafile location/datafile_name.dbf ' size 30m;
Example:
SQL> alter tablespace test add datafile
'/oradata/oracle/db/test/test02.dbf ' size 30M;

Deleting a datafile from Tablespace:

Syntax:
SQL> alter tablespace tablespace_name drop datafile
'datafile location/datafile_name.dbf'

Example:
SQL> alter tablespace test drop datafile
‘/oradata/oracle/db/test/test02.dbf’;

Droping a Tablespace:

Sql> drop tablespace test;       (It will drop tablespace and datafile at database level)

Sql> drop tablespace test including contents and datafiles;
(It will drop tablespace logically(database level) and physically(o/s level))

Reusing Orphan datafile:

By using "drop tablespace tablespace_name" command we can drop tablespace at
logical level but physically those datafiles are exist and we can reuse those datafile (Orphan) by
using following command.

Syntax:
SQL> create tablespace tablespace_name datafile
'datafile location/datafile_name.dbf' reuse;

Example:
SQL> create tablespace test datafile
'/oradata/oracle/db/test/test02.dbf' reuse;

Making a Tablespace as read only:

SQL> alter tablespace test read only;

SQL> select tablespace_name,status from dba_tablespaces;   To view the status of tablespace

SQL> alter tablespace test read write;

Making a Tablespace offline:

Sql> alter tablespace test offline;  Users can not access this tablespace in this state.

Sql> alter tablespace test online;

Renaming of Tablespace:

Syntax:
SQL> alter tablespace old_tablespace TO new_tablespace;

Example:
SQL> alter tablespace test rename to test2;

Renaming a Datafile in Tablespace:

Steps:-
1.make Tablespace offline
SQL> alter tablespace test offline;

2. at os level rename the datafile
$cd   /oradata/oracle/db/test/
$mv   test01.dbf    test005.dbf
3. Update the Controlfile for this Datafile.
SQL> alter database rename file ‘/oradata/oracle/db/test/test01.dbf’ to
‘/oradata/oracle/db/test/test005.dbf;

4. Online the Tablespace
Sql> alter tablespace test online;

Select tablespace_name, file_name from dba_data_files;

Relocating a Datafile in Tablespace:

Steps:-
1.make Tablespace offline
SQL> alter tablespace test offline;

2. at os level rename the datafile
$cd    /oradata/oracle/db/test/
$mv   test01.dbf    /u01/test/test01.dbf
3. update the Controlfile for this datafile.
SQL> alter database rename file ‘/oradata/oracle/db/test/test01.dbf’ to
‘/u01/test/test01.dbf’;

4. online the Tablespace
SQL> alter tablespace test online;

SQL>Select tablespace_name,file_name from dba_data_files;

To check Database size:

SQL> Select sum(bytes)/1024/1024 “size in MB” from dba_data_files;

To check free space in Database:

SQL> Select sum(bytes)/1024/1024 from dba_free_space;

Views:-
V$tablespace V$datafile
Dba_tablespaces
User_tablespaces
Dba_data_files Dba_segments            
Dba_extents sm$ts_free
Sm$ts_used sm$ts_avail

Resize Datafiles:-

Syntax:
SQL> alter database datafile 'Location of Datafile/datafile_name.dbf' 

           resize 20M;

Example:
SQL> alter database test02
datafile '/oradata/oracle/db/test/test02.dbf'  resize 20M;

Create Temporary Tablespace:-

Syntax:
SQL> create temporary tablespace tempTB_name
tempfile 'temp file location.tempfile_name.dbf'  size 20M;
Example:
SQL> create temporary tablespace temp_test
tempfile '/oradata/oracle/db/test/temp_test.dbf' size 20M;

(v$sort_segment, v$tempseg_usage for the space info of temp t.s)

Create undo Tablespace:-

Syntax:
SQL> create undo tablespace undoTB_name
datafile 'location of datafile/datafile_name.dbf' size 50m;
Example:
SQL> create undo tablespace undotbs02
datafile '/oradata/oracle/db/test/undotbs02.dbf'  size 50M;

Adding Datafile to Undo Tablespace:

SQL> alter tablespace undotbs02
add datafile '/oradata/oracle/db/test/undotbs03.dbf'  size 50m;

Switching Undo Tablespaces:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs02 scope=both;

Views for undo Tablespace:

V$undostat
V$rollstat
V$transaction
dba_undo_extents

View for Sysaux tablespace:
V$sysaux_occupants

Managing the Online Redo Logs

                  The online redo logs are Oracle’s means of ensuring that all the changes made by the users are logged, in case there’s a failure before those changes can be written to permanent storage. Thus, redo logs are fundamental for the recovery process.
Oracle organizes its redo log files in redo log groups, and you need to have at least two different groups of redo logs with at least one member in each. You need to have at least two redo groups, because even when one redo log is being archived, the log writer should be able to write to an active redo log. Although your database will run just fine with only one member in each redo log group, Oracle strongly recommends that you multiplex the online redo logs. Multiplexing simply means that you maintain more than one member in each of your redo log groups. All members of a redo log group are identical—multiplexing is designed to protect against the loss of a single copy of a log file. When you multiplex the online redo log files, the log writer writes simultaneously to all the members of a group.

Creating Online Redo Log Groups:- 

Below command will add a new Redolog Group and its members.
Syntax:
 SQL> alter database add logfile group 4 ('logfile_location/log01.log','logfile_location/log01.log') size 10M;
Example: 
SQL> alter database add logfile group 4 
('/oradata/oracle/datafiles/catalog/redo04.log',
 '/oradata/oracle/datafiles/catalog/redo05.log') size 10M;

Adding Member to a existing Group:-

Syntax:-

SQL> alter database add logfile member 
'Log_file_location/name.log' to group 1;

Example:-

sql>alter database add logfile member
'/oradata/oracle/datafiles/catalog/redo01_02.log' to group 1;

Note: We don't have to specify the size for new redo log member being added to group 1 the new member will simply be sized the same as the existing members of the group.

Rename Redolog Files:-

1. Shut down the database and start it up in the mount mode:
SQL> shutdown immediate
SQL> startup mount 

2. Move the files to the new location with an operating system command:
$ mv /oradata/oracle/datafile/redo01.log /u01/datafiles/redo001.log

3. Use the ALTER DATABASE RENAME datafile command to rename the file within the control file:
SQL> alter database rename file '/oradata/oracle/datafile/redo01.log'
to '/u01/datafiles/redo001.log';

Dropping Online Redo Logs:-

You can drop an entire redo log group by using the following command:
SQL> alter database drop logfile group 4;

To drop a single member of an online redo log group, use this command:
SQL> alter database drop logfile member
 '/u01/datafiles/mul_redo01.log';

If the redo log file you want to drop is active, Oracle won’t let you drop it. You need to use the following command to switch the log file first, after which you can drop it:
SQL> alter system switch logfile;

Online Redo Log Corruption:-

You can set the DB_BLOCK_CHECKSUM initialization parameter to make sure Oracle checks for corruption in the redo logs before they’re archived. 

If the online redo logs are corrupted, the file can’t be archived, and one solution is to just drop and re-create them. But if there are only two log groups, you can’t do this, as Oracle insists on having a minimum of two online redo log groups at all times. However, you can create a new (3rd) redo log group, and then drop the corrupted redo log group. Also, you can’t drop an online redo log file if the log file is part of the current group. Your strategy then would be to reinitialize the log file by using the following statement:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
If the log group has not been archived yet, you can use the following statement:
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

Monitoring the Redo Logs:-

You can use two key dynamic views, V$LOG and V$LOGFILE, to monitor the online redo logs.
The V$LOGFILE view provides the full filename of the redo logs, their status, and type

SQL>select * from v$logfile;
 

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3  ONLINE /oradata/oracle/datafiles/catalog/redo03.log NO
2  ONLINE /oradata/oracle/datafiles/catalog/redo02.log NO
1  ONLINE /oradata/oracle/datafiles/catalog/redo01.log NO
The V$LOG view gives detailed information about the size and status of the redo logs, as well as showing whether the logs have been archived:
SQL> select group#, sequence#, bytes/1024/1024, archived, members from v$log;

GROUP# SEQUENCE# BYTES ARC MEMBERS
---------- ---------- --------------- --- ----------    
1             0             50       YES       1
2            55            50       NO        1
3           54             50       YES       1