Friday, May 28, 2010

prepare ASM disk, create ASM instance & diskgroups from scratch

Links to this post
login as root user

[root@TEST6 ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 637 5116671 83 Linux
/dev/sda2 638 2167 12289725 83 Linux
/dev/sda3 2168 2418 2016157+ 83 Linux
/dev/sda4 2419 2610 1542240 5 Extended
/dev/sda5 2419 2609 1534176 82 Linux swap

Disk /dev/sdb: 3221 MB, 3221225472 bytes
255 heads, 63 sectors/track, 391 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

FIRST PARTITION

[root@TEST6 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-391, default 1): 1
Last cylinder or +size or +sizeM or +sizeK (1-391, default 391): 125

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@TEST6 ~]#

SECOND PARTITION

[root@TEST6 ~]# fdisk /dev/sdb

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (126-391, default 126): 126
Last cylinder or +size or +sizeM or +sizeK (126-391, default 391): 275

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

THIRD PARTITION

[root@TEST6 ~]# fdisk /dev/sdb

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 3
First cylinder (276-391, default 276):
Using default value 276
Last cylinder or +size or +sizeM or +sizeK (276-391, default 391):
Using default value 391

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.


WHICH PARTITION ARE UNBOUND
[root@TEST6 ~]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 5036284 3456608 1323844 73% /
none 375068 0 375068 0% /dev/shm
/dev/sda2 12096756 1662808 9819464 15% /u00
/dev/sda3 1984428 35784 1847840 2% /u01
/dev/hdc 116020 116020 0 100% /media/cdrom

[root@TEST6 ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 637 5116671 83 Linux
/dev/sda2 638 2167 12289725 83 Linux
/dev/sda3 2168 2418 2016157+ 83 Linux
/dev/sda4 2419 2610 1542240 5 Extended
/dev/sda5 2419 2609 1534176 82 Linux swap

Disk /dev/sdb: 3221 MB, 3221225472 bytes
255 heads, 63 sectors/track, 391 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 125 1004031 83 Linux ------> CANT FIND IN df -k
/dev/sdb2 126 275 1204875 83 Linux ------> CANT FIND IN df -k
/dev/sdb3 276 391 931770 83 Linux-------> CANT FIND IN df -k

[root@TEST6 ~]# vi /etc/sysconfig/rawdevices
edit
/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdb2
/dev/raw/raw3 /dev/sdb3
~
[root@TEST6 ~]# service rawdevices restart
Assigning devices:
/dev/raw/raw1 --> /dev/sdb1
/dev/raw/raw1: bound to major 8, minor 17
/dev/raw/raw2 --> /dev/sdb2
/dev/raw/raw2: bound to major 8, minor 18
/dev/raw/raw3 --> /dev/sdb3
/dev/raw/raw3: bound to major 8, minor 19
done
[root@TEST6 ~]#

[root@TEST6 ~]# chown oracle:dba /dev/raw/raw1
[root@TEST6 ~]# chown oracle:dba /dev/raw/raw2
[root@TEST6 ~]# chown oracle:dba /dev/raw/raw3
[root@TEST6 ~]# chown 660 /dev/raw/raw[1-3]
[root@TEST6 ~]# ls -lart /dev/raw/raw*
crw-rw---- 1 oracle dba 162, 1 May 28 15:43 /dev/raw/raw1
crw-rw---- 1 oracle dba 162, 2 May 28 15:43 /dev/raw/raw2
crw-rw---- 1 oracle dba 162, 3 May 28 15:43 /dev/raw/raw3

loging as oracle
[root@TEST6 ~]#su - oracle
AS ORACLE USER

test6-> mkdir -p /u00/oradata/test/asmdisk1
test6-> mkdir -p /u00/oradata/test/asmdisk2
test6-> mkdir -p /u00/oradata/test/asmdisk4
test6-> ln -sf /dev/raw/raw1 /u00/oradata/test/asmdisk1
test6-> ln -sf /dev/raw/raw2 /u00/oradata/test/asmdisk2
test6-> ln -sf /dev/raw/raw3 /u00/oradata/test/asmdisk3

[root@TEST6 ~]# vi /etc/udev/permissions.d/50-udev.permissions
# raw devices
ram*:root:disk:0660
raw/*:oracle:dba:0660

[root@TEST6 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]
[root@TEST6 ~]#

[root@TEST6 ~]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "/dev/sdb1" as an ASM disk: [ OK ]
[root@TEST6 ~]# /etc/init.d/oracleasm createdisk VOL2 /dev/sdb2
Marking disk "/dev/sdb2" as an ASM disk: [ OK ]
[root@TEST6 ~]# /etc/init.d/oracleasm createdisk VOL3 /dev/sdb3
Marking disk "/dev/sdb3" as an ASM disk: [ OK ]
[root@TEST6 ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
[root@TEST6 ~]#

AS ORACLE USER

test6-> mkdir -p $ORACLE_BASE/admin/+ASM/bdump
test6-> mkdir -p $ORACLE_BASE/admin/+ASM/cdump
test6-> mkdir -p $ORACLE_BASE/admin/+ASM/hdump
test6-> mkdir -p $ORACLE_BASE/admin/+ASM/pfile
test6-> mkdir -p $ORACLE_BASE/admin/+ASM/udump

INITFILE OF ASM

--------------------------------------------------------------

###########################################
# Automatic Storage Management
###########################################
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u00/app/oracle/admin/+ASM/bdump
core_dump_dest=/u00/app/oracle/admin/+ASM/cdump
user_dump_dest=/u00/app/oracle/admin/+ASM/udump

###########################################
# Miscellaneous
###########################################
instance_type=ASM
compatible=10.1.0.4.0

###########################################
# Pools
###########################################
large_pool_size=12M

###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=exclusive

--------------------------------------------------------------


vi /u00/app/oracle/admin/+ASM/pfile/init.ora
ln -s $ORACLE_BASE/admin/+ASM/pfile/init.ora $ORACLE_HOME/dbs/init+ASM.ora

AS ROOT USER GO TO $ORACLE_HOME/bin

[root@TEST6 bin]# ./localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 90 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
test6
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)



test6-> ORACLE_SID=+ASM; export ORACLE_SID
test6-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 28 16:43:22 2010

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

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> create spfile from pfile;

File created.

SQL> alter system set asm_diskstring = '/dev/raw/raw*';

System altered.

SQL> create diskgroup DATA1 external redundancy disk '/dev/raw/raw1';

Diskgroup created.


Sunday, May 23, 2010

database characteristics

Links to this post
VERSION
select banner from v$version;

COMPONENT INSTALLED

select COMP_NAME,COMP_ID from dba_registry;

CHARACTERSET
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

TRACE LOCATIONS
select * from v$parameter where value like '%/%;

TIMEZONE
select dbtimezone from dual;

DIRECTORIES
select directory_path from dba_directories;

This is the view could tell you most of about database operating characteristics...

NLS_DATABASE_PARAMETERS

Saturday, May 22, 2010

Upgradge 10g RAC upto 10204 | applying patch on 10g RAC

Links to this post
we will upgrade 10201 to 10204 in two phase for RAC.

first we will upgrade CRS clusterware.
second we will upgrade db sofware.


if you get
xlib
cant display 0.0 error
the easiest method is
do
xhost + from root user

then check xclock from oracle user.

Now
run the ./runinstaller from DISK1 of patch
















first we will just upgrade clusterware...
so specify clusterware home and path.















specify all nodes , choose cluster installation..















check below pre-requisite for any major errors















follow the instruction at end of installation















you must watch successfully applied word at the end of running root102.sh































After running root102.sh on clusterware















lets query version of CRS has been upgrade to 10204 or not ??

crsctl query css activeversion
crs active version on the cluster is [10.2.0.4]

NOW ITS TIME TO UPGRADE DB SOFTWARE.
before we start upgrade our db software please make sure listener is shutdown
cs_stat -t has to be look like this one
















select HOME name and path.




























































run root.sh by root user
















Note: change database parameter cluster_database = false

open instance in upgrade mode and run catupg.sql















after that
NOTE: change database parameter cluster_database = true

startup database

select * from v$version;

database views

Links to this post
DATABASE PROPERTIES=
DATABASE_PROPERTIES,V$NLS_PARAMETERS
DATABASE=
V$DATABASE, V$VERSION, V$INSTANCE
SHARED SERVER =
V$QUEUE, V$DISPATCHER, V$SHARED_SERVER
CONNECTION POOLING=
DBA_CPOOL_INFO, V$CPOOL_STAT, V$CPOOL_CC_STATS
SPACE=
DBA_DATA_FILES,DBA_FREE_SPACE,DBA_TS_QUOTAS
CONTROLFILES=
V$CONTROLFILE, V$PARAMETER, V$CONTROLFILE_RECORD_SECTION
DATAFILES=
V$DATAFILE,V$DATAFILE_HEADER, DBA_DATA_FILES
SEGMENTS=
DBA_SEGMENTS, USER_SEGMENTS
EXTENTS=
DBA_EXTENTS, USER_EXTENTS
REDOLOG=
V$THREAD, V$LOG, V$LOGFILE, V$LOG_HISTORY
UNDO=
V$UNDOSTAT, V$ROLLSTAT, V$TRANSACTION
ARCHIVE=
V$DATABASE, V$LOG, V$ARCHIVED_LOG, V$ARCHIVE_DEST
INSTANCE=
V$INSTANCE, V$PARAMETER, V$SYSTEM_PARAMETER
MEMORY=
V$SGA, V$SGASTAT, V$SGAINFOV$SGA_DYNAMIC_COMPONENTS,V$SGA_RESIZE_OPS
ADVICE=
V$SGA_TARGET_ADVICE, V$PGA_TARGET_ADVICE
PROCESSES=
V$PROCESS, V$BGPROCESS
PERFORMANCE=
V$SYSSTAT,V$SESSMETRIC,V$SYSTEM_EVENT
ALERTS=
DBA_THRESHOLDS, DBA_OUTSTANDING_ALERTS,DBA_ALERT_HISTORY, V$ALERT_TYPES,V$METRIC
LOCK & LATCH=
V$LOCK,v$LOCKED_OBJECTS,DBA_LOCK, V$SESSION_WAIT,V$LATCH
TABLES=
DBA_TABLES, ALL_TABLES, USER_TABLES
INDEXES=
DBA_INDEXES, ALL_INDEXES, USER_INDEXES
DATAPUMP=
DBA_DATAPUMP_JOBS,DBA_DATAPUMP_SESSIONS,(V$SESSION_LONGOPS|SOFAR|TOTALWORK|UNITS)
OBJECTS=
DBA_OBJECTS, ALL_OBJECTS, USER_OBJECTS
ASM=
V$ASM_DISKGROUP,V$ASM_FILE,V$ASM_OPERATION,V$ASM_ALIAS,V$ASM_CLIENT,V$ASM_DISK)
FLASH RECOVERY=
V$RECOVERY_FILE_DEST
TRASPORT PLATFORM=
V$TRANSPORTABLE_PLATFORM
RECYCLE BIN=
DBA_RECYCLEBIN
MODIFIED TABLE=
DBA_TAB_MODIFICATIONS (since last stats gather)
SQL TRACES=
DBA_ENABLED_TRACES
DATA DICTIONARY HIT RATIO=
V$ROWCACHE
LIBRARY CACHE | RELOAD RATIO
V$LIBRARYCACHE
HIST TABLES=
v$ACTIVE_SESSION_HISTORY,DBA_HIST_SQLSTAT,DBA_HIST_SQLTEXT,DBA_HIST_SQLPLAN