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

How to compile invalid objects in oracle

SELECT CASE object_type
  2         WHEN 'PACKAGE' THEN
  3          'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
  4         ELSE
  5          'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
  6         END
  7    FROM dba_objects
  8   WHERE STATUS = 'INVALID'
  9    and owner = 'TESTCMS'
 10     AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');

CASEOBJECT_TYPEWHEN'PACKAGE'THEN'ALTER'||OBJECT_TYPE||''||OWNER||'.'||OBJECT_NAM
--------------------------------------------------------------------------------
ALTER PACKAGE TESTCMS.BCE_DOWNLOAD_LIST_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_ITEM_DISPLAY_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_EDIT_CONTENT_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_IMAGE_GALLERY_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_RSS_FEED COMPILE BODY;

For function and procedure use:
alter function TESTCMS.object_name compile;
alter trigger schema.object_type compile;
alter procedure schema.object_type compile;

alter trigger PORTAL.WWDOC_DOCU_BRU_TRG compile;

=====================
TESTCMS                        BCE_CMS_NOTIFICATION_PORTLET                                                                                                     PACKAGE BODY
TESTCMS                        BCE_CMS_APPROVAL                                                                                                                 PACKAGE BODY

ALTER PACKAGE TESTCMS.BCE_CMS_NOTIFICATION_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_APPROVAL COMPILE BODY;

Oracle Text indexes synchronisation

PURPOSE=======
To show how the Oracle Text indexes are synchronized and how to control it. 

SCOPE & APPLICATION===================
This note is for Oracle Text administrators and developers. 

Synchronizing Text Indexes
A text index always responds within the same transaction to DELETE in the indexed column, but the response to INSERT and UPDATE which is modeled as a DELETE followed by an INSERT is asynchronous. An internal trigger which isinvisible to the customer requests index maintenance by inserting a row intoa queue table in CTXSYS schema for each new row in the indexed column.This DML queue can be tracked by querying the CTX_PENDING or CTX_USER_PENDINGText data dictionary view. 
Example: 
  create table docs (id number primary key, doc varchar2(60));
  create index i_doc on docs (doc) indextype is ctxsys.context; 
  insert into docs values (1,'A man walking on the street with a barking dog.'); 
  select * from ctx_user_pending; 
  PND_INDEX_NAME                 PND_ROWID          PND_TIMES   ------------------------------ ------------------ ---------   I_DOC                          AAAHVoAAEAAAEiqAAA 14-NOV-06 
  select * from docs where contains(doc,'dog')>0; 
  no rows selected 
In this situation the table and the text index on a column of this table are notsynchronized. A text query on the indexed column does not return the new row eventhough the new document fulfills the condition. So the table and the index have to be synchronized. The above defined DML queue contains information about rows in the document tableto be synchronized with the text index. We say that the table is synchronized withits text index when there is no entry referencing to this text index in the DML queue. 
After synchronizing the table and its index text queries return with the new/updateddocuments fulfilling the condition. Of course, the entry referencing the rows to besynchronized disappear from the DML queue. 
Example: 
  select * from docs where contains(doc,'man')>0; 
         ID DOC   --------- ----------------------------------------------------------------           1 A man walking on the street with a barking dog 
  select * from ctx_user_pending; 
  no rows selected 
There are two approaches for synchronization: 
  1. Explicit one-shot request for a given index   2. Implicit continuous request for all indexes database-wide 
1. Explicit Synchronization 
Explicit synchronization can be implemented using ctx_ddl.sync_index PL/SQL procedure.
  begin   ctx_ddl.sync_index(idx_name => '<index_name>', memory => '50M');  end;
The memory parameter determines the maximum memory which will be allocated by theprocess which synchronizes the table and the index. Its default and maximum valueis determined by the DEFAULT_INDEX_MEMORY and MAX_INDEX_MEMORY system parameterrespectively which can be queried from CTX_PARAMETERS Text data dictionary viewand can be modified with ctx_adm.set_parameter PL/SQL procedure. It is worth setting larger values for faster completion and there will be fewer recordsper token in the token table. 

PL/SQL procedures have to be written for implementing synchronization in order to start jobs running automatically. Defining jobs requires at least one job queue process running. 
Example: 
  create or replace procedure my_index_sync   is   begin     ctx_ddl.sync_index( idx_name => 'my_index', memory => '50M');   end;   / 
  declare   v_job number;     begin    dbms_job.submit( job=>v_job, what=>'my_index_sync;', next_date=>sysdate, interval=>'sysdate+1/24');  end;   / 
One of the advantages of this method is to make differences between indexes,i.e. less important indexes can be syncronized rarely and more important often.So synchronization policy can be implemented for each text index. On the other hand, DBA always has to keep track the job system and if a new text index is created in the database DBA has to look after its synchronization. 

2. Explicit Synchronization for all text indexes database-wide 
This is a short script that runs sync in a loop with a variable delay.It remains logged on, continually scanning for DML to do on any index.
Note that this script will never exit on its own.

create table stop_sync(aa varchar(10)); declare  the_count integer;  sleep_time number := 5;begin  loop    -- scan and process pending dml to do    ctx_ddl.sync_index('null');    select count(*) into the_count from stop_sync;    exit when the_count > 0;    -- sleep for 5 seconds to avoid spinning    dbms_lock.sleep(sleep_time);  end loop;  delete from stop_sync;  commit;end;/
To stop it, you simply insert a row into the table "stop_sync" from another session.
insert into stop_sync values ('X');commit;

How to check which are the object cached in memory

 How to check which are the object cached in memory:-

select SUBSTR(owner,1,10) Owner,SUBSTR(Type,1,12) Type,SUBSTR(name,1,20) Name,
executions,sharable_mem Mem_used,
SUBSTR(Kept||' ',1,4) "Kept"
from v$db_object_cache
where type in('trigger','procedure','Package Body','Package')
order by executions desc;