How to view the Checkpoint activites in Oracle Database

How to check to how many times you have to scan the short and long tables.
SQL> SELECT name, value
FROM v$sysstat
WHERE name IN ('table scans (short tables)',
'table scans (long tables)')
/
Try to reduce the number by creating proper table indexes. Note that the count for 'Long Tables scan' must be very small.


How to view the Checkpoint activites
Query the V$SYSSTAT directory view for the checkpoint process activity to monitor the checkpoint process.
SQL> SELECT name, value
FROM v$sysstat
WHERE name like 'background check%'
/
If the "background check started" value is greater than the "background check completed" value, you should increase the size of the REDO LOG files.


How to monitor REDO log file parallel write
Query the V$SYSTEM_EVENT directory view to see the Log File Parallel Write Event to monitor and tune a Redo Log file.
SQL> SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event = 'log file parallel write'
/
The "Waits" column indicates a possible I/O problem.

How to create a ASM instance in RAC

How to create a ASM instance in RAC:
 Step1.    Start cluster synchronization service:
        >crs_start ora.cssd
                     >crs_stat –t (to check the status)
Step2.   Register ASM instance to OCR
>export ORACLE_SID=+ASM
>srvctl add asm -p $ORACLE_HOME/dbs/init+ASM.ora
              > Start the ASM instance any way you like:
              > srvctl start asm
        or
        > sqlplus "/ as sysasm"
        startup
        and check the status
        > srvctl status asm

Init+ASM.ora contents:
instance_type=asm
remote_login_passwordfile=exclusive
Step3.    Create Disk groups
Two Raw devices d100 and d103 of size 165GB and 20GB respectively allocated to ASM.

> sqlplus "/ as sysasm"
CREATE DISKGROUP DATA_AB01 EXTERNAL REDUNDANCY DISK '/dev/ad/rdsk/d101';

CREATE DISKGROUP FRA_AB01 EXTERNAL REDUNDANCY DISK '/dev/ad/rdsk/d102';

How to install and create an ASM disks in Oracle RAC system:-

How to install and create an ASM disks in Oracle RAC system:-

All the steps mentioned are as per my own test cases.
Pl make sure to test for your own environment:

Download the latest Oracle ASM RPMs from http://otn.oracle.com/tech/linux/asmlib/index.html.

Make sure that you download the right ASM driver for your kernel (UP or SMP). 

To install the ASM RPMs on a UP server, run: 
su - root
rpm -Uvh oracleasm-2.4.21-EL-1.0.0-1.i686.rpm \
         oracleasm-support-1.0.2-1.i386.rpm \
         oracleasmlib-1.0.0-1.i386.rpm
To install the ASM RPMs on a SMP server, run: 
su - root
rpm -Uvh oracleasm-2.4.21-EL-smp-1.0.0-1.i686.rpm \
         oracleasm-support-1.0.2-1.i386.rpm \
         oracleasmlib-1.0.0-1.i386.rpm

Configuring and Loading ASM

To load the ASM driver oracleams.o and to mount the ASM driver filesystem, enter: 
su - root
# /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 []: oinstall
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  ]
Creating /dev/oracleasm mount point                        [  OK  ]
Loading module "oracleasm"                                 [  OK  ]
Mounting ASMlib driver filesystem                          [  OK  ]

Scanning system for ASM disks                              [  OK  ]

#


Creating ASM Disks

NOTE: Creating ASM disks is done on one RAC node! The following commands should only be executed on one RAC node!

I executed the following commands to create my ASM disks: (make sure to change the device names!)
(In this example I used partitions (/dev/sda2, /dev/sda3, /dev/sda5) instead of whole disks (/dev/sda, /dev/sdb, /dev/sdc,...)) 
su - root
# /etc/init.d/oracleasm createdisk VOL1 /dev/<sd??>
Marking disk "/dev/sda2" as an ASM disk                    [  OK  ]
# /etc/init.d/oracleasm createdisk VOL2 /dev/<sd??>
Marking disk "/dev/sda3" as an ASM disk                    [  OK  ]
# /etc/init.d/oracleasm createdisk VOL3 /dev/<sd??>
Marking disk "/dev/sda5" as an ASM disk                    [  OK  ]
#
# # Replace "sd??" with the name of your device. I used /dev/sda2, /dev/sda3, and /dev/sda5

To list all ASM disks, enter: 
# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
#

On all other RAC nodes, you just need to notify the system about the new ASM disks: 
su - root
# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks                              [  OK  ]
#

 

How to get or extract year,month,day fom date or timestamp field of a table in oracle

How to extract year,month,day  from timestamp or date field of a table in oracle:
 Here service start date is the column name:
 
   SELECT EXTRACT(YEAR FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL
2009
2010
2011
2012
2010
   
   SELECT min(EXTRACT(YEAR FROM service_start_date)) FROM apcadm.apc_info_service where service_start_date IS NOT NULL 

1998
getting a count wise report:

 SELECT count(info_fk), EXTRACT(YEAR FROM service_start_date) FROM   apcadm.apc_info_service where service_start_date IS NOT NULL
   group by EXTRACT(YEAR FROM service_start_date)
   order by 2;
Same way we can extract the month number and day of the month:

 SELECT EXTRACT(month FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL
  
   SELECT EXTRACT(day FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL