OCR Replacement-Replace Existing OCR Disk with New One-Oracle RAC 11GR2

OCR Replacement-Replace Existing OCR Disk with New One-Oracle RAC 11GR2

----------------------Prerequisite for Activity--------------

1.OCR Backup Manually & Automatically to other location:

You can use the ocrconfig -manualbackup command to force Oracle Clusterware to perform a backup of the OCR at any time, rather than wait for the automatic backup that occurs at 4-hour intervals.
3$GRID_HOME/bin
./ocrconfig -manualbackup
./ocrconfig -showbackup

2.Export OCR Backup

./ocrconfig -export /tmp/ocr_export_date.exp

3.Import OCR Backup

---------------------Voting Disk----------------------------------

1.Require 3 Luns of 500 Mb each of different-2 storage.

/dev/mapper/VOTE_V1  ---Violin Storage
/dev/mapper/VOTE_H1 ----Hitachi  Storage
/dev/mapper/VOTE_V2 ----Violin Storage
Create 3 Luns of 500Mb each. If possible put each Lun in different controller, array or storage.

2.DISK Creation from root user on one of the Node(node 1) i.e 1.***.43.**

2.1) For Voting

oracleasm createdisk VOTE_V1 /dev/mapper/VOTE_V1
oracleasm createdisk VOTE_H1 /dev/mapper/VOTE_H1
oracleasm createdisk VOTE_V2 /dev/mapper/VOTE_V2
oracleasm scandisks
oracleasm listdisks

3.SCAN DISK ON Another Node(node 2) i.e 1.***.43.** from root user.

oracleasm scandisks

4.Create Voting Diskgroup with NORMAL REDUNDANCY along with three failgroup.

CREATE DISKGROUP VOTE_GRP NORMAL REDUNDANCY
 FAILGROUP VOTE_FAIL_V1 DISK 'ORCL:VOTE_V1'
 FAILGROUP VOTE_FAIL_H1 DISK 'ORCL:VOTE_H1'
 FAILGROUP VOTE_FAIL_V2 DISK 'ORCL:VOTE_V2'
ATTRIBUTE 'compatible.asm' = '11.2.0.1.0';

---------------------OCR Disk----------------------------------

1.Require 2 Luns of 500 Mb each of different-2 storage.

/dev/mapper/CRS_V1  ---Violin Storage
/dev/mapper/CRS_H1  ----Hitachi  Storage

2.DISK Creation from root user on one of the Node 1 i.e 1.***.43.**

oracleasm createdisk CRS_V1 /dev/mapper/CRS_V1
oracleasm createdisk CRS_H1 /dev/mapper/CRS_H1
oracleasm scandisks
oracleasm listdisks

3.SCAN DISK ON Another Node(node 2) i.e 1.***.43.** from root user.

oracleasm scandisks

4.Create CRS Diskgroup with NORMAL REDUNDANCY along with two failgroup.

CREATE DISKGROUP CRS_GRP NORMAL REDUNDANCY
 FAILGROUP CRS_FAIL_V1 DISK 'ORCL:CRS_V1'
 FAILGROUP CRS_FAIL_H1 DISK 'ORCL:CRS_H1'
 ATTRIBUTE 'compatible.asm' = '11.2.0.1.0';

---------------------------------Verification----------------------------------

TO check Diskgroup Provissioned/Not

SET LINESIZE 150
 COL PATH FOR A30
COL NAME FOR A10
COL HEADER_STATUS FOR A20
COL FAILGROUP FOR A20
 COL FAILGROUP_TYPE FOR A20
COL VOTING_FILE FOR A20
SELECT  NAME,PATH,HEADER_STATUS,FAILGROUP, FAILGROUP_TYPE, VOTING_FILE
FROM  V$ASM_DISK
WHERE  GROUP_NUMBER IN ( SELECT GROUP_NUMBER FROM V$ASM_DISKGROUP WHERE NAME IN ('CRS_GRP','VOTE_GRP'));

             Mount the diskgroup on another node (node2)i.e 1.***.43.**-

#alter diskgroup VOTE_GRP mount;
#alter diskgroup CRS_GRP mount;

-----------------------CURRENT OCR/VOTING CONFIGURATION---------

------------------------OCR--------------------------------------

[root@NEWDB1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3768
Available space (kbytes) : 258352
ID : 1579853954
Device/File Name : +OCRDATA
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded

------------------------VOTING--------------------------------------

[grid@NEWDB1 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 8f6bd01f079d4f06bf3beb78f18e9bfc (ORCL:OCRDATA1) [OCRDATA]
Located 1 voting disk(s).

Moving Voting Files from existing disk group to New DiskGroup

This should be run during Maintaince Window.Instances should be down of all the nodes.

============From grid user under GRID_HOME

$crsctl replace votedisk VOTE_GRP   (VOTE_GRP is a new Diskgroup for Voting file)
$crsctl query css votedisk
To check current voting disk configuration
TO check Diskgroup Provissioned/Not
SET LINESIZE 150
COL PATH FOR A30
COL NAME FOR A10
COL HEADER_STATUS FOR A20
COL FAILGROUP FOR A20
COL FAILGROUP_TYPE FOR A20
COL VOTING_FILE FOR A20
SELECT  NAME,PATH,HEADER_STATUS,FAILGROUP, FAILGROUP_TYPE, VOTING_FILE
FROM  V$ASM_DISK
WHERE  GROUP_NUMBER IN ( SELECT GROUP_NUMBER FROM V$ASM_DISKGROUP WHERE NAME IN ('VOTE_GRP'));

Moving OCR Files from existing disk group to New DiskGroup

# /u01/app/11.2.0/grid/bin/ocrconfig -add +CRS_GRP
#/u01/app/11.2.0/grid/bin/ocrcheck
# /u01/app/11.2.0/grid/bin/ocrconfig -add +VOTE_GRP
#/u01/app/11.2.0/grid/bin/ocrcheck

--------------------Replace Existing OCR Disk with New One------------------------------

# ocrconfig -replace +OCRDATA -replacement +CRS_GRP

DELETE EARLIER DISKGROUP INFORMATION FROM OCR

#/u01/app/11.2.0/grid/bin/ocrconfig -delete +OCRDATA
#/u01/app/11.2.0/grid/bin/ocrcheck

=================DROP OLD OCR DISKGROUP LATER----------------------

login from grid user & connected as sys as sysasm
sql>alter diskgroup OCRDATA dismount;  #It should dismount before drop#####
sql>alter diskgroup OCRDATA mount;
sql>drop diskgroup OCRDATA including contents;

Table Level Export Import in 9i Oracle Database-Most Time Effective Way

Table Level Export Import in 9i Oracle Database-Most Time Effective Way

From Source database copy the table creation script using below command:

select dbms_metadata.get_ddl('TABLE','BB_CONNECTION_HISTORY_12AUG','LMSOGS') from dual;

Create the table structure in destination database under same user using the script taken from source:

 CREATE TABLE "LMSOGS"."BB_CONNECTION_HISTORY_12AUG"

   (    "KAR_ACC_ORG" NUMBER(3,0) NOT NULL ENABLE,

        "KAR_ACC_ACCT" VARCHAR2(19) NOT NULL ENABLE,

        "KAR_REC_TYPE" VARCHAR2(1) NOT NULL ENABLE,

        "KAR_CURRENCY" VARCHAR2(3) NOT NULL ENABLE,

        "KAR_ACC_INSERT_DT" DATE NOT NULL ENABLE,

        "KAR_REC_STATUS" VARCHAR2(1),

        "KAR_LOGO" NUMBER(3,0),

        "KAR_FIAT_OCCURRENCE_1" NUMBER(3,0),

        "KAR_FIAT_DATE_1" DATE,

        "KAR_FIAT_CYCLE_DUE_1" NUMBER(1,0),

        "KAR_FIAT_RECENCY_1" NUMBER(1,0),

        "KAR_FIAT_REVERSAL_IND_1" NUMBER(1,0),

        "KAR_FIAT_PMT_AMT_1" NUMBER(23,6),

        "KAR_FIAT_PRE_PMT_AMT_1" NUMBER(23,6),

        "KAR_FIAT_AMT_TOT_DUE_1" NUMBER(23,6),

        "KAR_FIAT_REL_PMT_AMT_1" NUMBER(23,6),

        "KAR_FIAT_ORIG_PMT_AMT_1" NUMBER(23,6),

        "KAR_FIAT_ORIG_LM_1" NUMBER(2,0),

        "KAR_FIAT_DPT_AMT_1_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_2_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_3_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_4_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_5_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_6_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_7_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_8_1" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_9_1" NUMBER(23,6),

        "KAR_FIAT_OCCURRENCE_2" NUMBER(3,0),

        "KAR_FIAT_DATE_2" DATE,

        "KAR_FIAT_CYCLE_DUE_2" NUMBER(1,0),

        "KAR_FIAT_RECENCY_2" NUMBER(1,0),

        "KAR_FIAT_REVERSAL_IND_2" NUMBER(1,0),

        "KAR_FIAT_PMT_AMT_2" NUMBER(23,6),

        "KAR_FIAT_PRE_PMT_AMT_2" NUMBER(23,6),

        "KAR_FIAT_AMT_TOT_DUE_2" NUMBER(23,6),

        "KAR_FIAT_REL_PMT_AMT_2" NUMBER(23,6),

        "KAR_FIAT_ORIG_PMT_AMT_2" NUMBER(23,6),

        "KAR_FIAT_ORIG_LM_2" NUMBER(2,0),

        "KAR_FIAT_DPT_AMT_1_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_2_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_3_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_4_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_5_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_6_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_7_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_8_2" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_9_2" NUMBER(23,6),

        "KAR_FIAT_OCCURRENCE_3" NUMBER(3,0),

        "KAR_FIAT_DATE_3" DATE,

        "KAR_FIAT_CYCLE_DUE_3" NUMBER(1,0),

        "KAR_FIAT_RECENCY_3" NUMBER(1,0),

        "KAR_FIAT_REVERSAL_IND_3" NUMBER(1,0),

        "KAR_FIAT_PMT_AMT_3" NUMBER(23,6),

        "KAR_FIAT_PRE_PMT_AMT_3" NUMBER(23,6),

        "KAR_FIAT_AMT_TOT_DUE_3" NUMBER(23,6),

        "KAR_FIAT_REL_PMT_AMT_3" NUMBER(23,6),

        "KAR_FIAT_ORIG_PMT_AMT_3" NUMBER(23,6),

        "KAR_FIAT_ORIG_LM_3" NUMBER(2,0),

        "KAR_FIAT_DPT_AMT_1_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_2_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_3_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_4_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_5_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_6_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_7_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_8_3" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_9_3" NUMBER(23,6),

        "KAR_FIAT_OCCURRENCE_4" NUMBER(3,0),

        "KAR_FIAT_DATE_4" DATE,

        "KAR_FIAT_CYCLE_DUE_4" NUMBER(1,0),

        "KAR_FIAT_RECENCY_4" NUMBER(1,0),

        "KAR_FIAT_REVERSAL_IND_4" NUMBER(1,0),

        "KAR_FIAT_PMT_AMT_4" NUMBER(23,6),

        "KAR_FIAT_PRE_PMT_AMT_4" NUMBER(23,6),

        "KAR_FIAT_AMT_TOT_DUE_4" NUMBER(23,6),

        "KAR_FIAT_REL_PMT_AMT_4" NUMBER(23,6),

        "KAR_FIAT_ORIG_PMT_AMT_4" NUMBER(23,6),

        "KAR_FIAT_ORIG_LM_4" NUMBER(2,0),

        "KAR_FIAT_DPT_AMT_1_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_2_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_3_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_4_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_5_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_6_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_7_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_8_4" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_9_4" NUMBER(23,6),

        "KAR_FIAT_OCCURRENCE_5" NUMBER(3,0),

        "KAR_FIAT_DATE_5" DATE,

        "KAR_FIAT_CYCLE_DUE_5" NUMBER(1,0),

        "KAR_FIAT_RECENCY_5" NUMBER(1,0),

        "KAR_FIAT_REVERSAL_IND_5" NUMBER(1,0),

        "KAR_FIAT_PMT_AMT_5" NUMBER(23,6),

        "KAR_FIAT_PRE_PMT_AMT_5" NUMBER(23,6),

        "KAR_FIAT_AMT_TOT_DUE_5" NUMBER(23,6),

        "KAR_FIAT_REL_PMT_AMT_5" NUMBER(23,6),

        "KAR_FIAT_ORIG_PMT_AMT_5" NUMBER(23,6),

        "KAR_FIAT_ORIG_LM_5" NUMBER(2,0),

        "KAR_FIAT_DPT_AMT_1_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_2_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_3_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_4_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_5_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_6_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_7_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_8_5" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_9_5" NUMBER(23,6),

        "KAR_FIAT_OCCURRENCE_6" NUMBER(3,0),

        "KAR_FIAT_DATE_6" DATE,

        "KAR_FIAT_CYCLE_DUE_6" NUMBER(1,0),

        "KAR_FIAT_RECENCY_6" NUMBER(1,0),

        "KAR_FIAT_REVERSAL_IND_6" NUMBER(1,0),

        "KAR_FIAT_PMT_AMT_6" NUMBER(23,6),

        "KAR_FIAT_PRE_PMT_AMT_6" NUMBER(23,6),

        "KAR_FIAT_AMT_TOT_DUE_6" NUMBER(23,6),

        "KAR_FIAT_REL_PMT_AMT_6" NUMBER(23,6),

        "KAR_FIAT_ORIG_PMT_AMT_6" NUMBER(23,6),

        "KAR_FIAT_ORIG_LM_6" NUMBER(2,0),

        "KAR_FIAT_DPT_AMT_1_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_2_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_3_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_4_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_5_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_6_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_7_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_8_6" NUMBER(23,6),

        "KAR_FIAT_DPT_AMT_9_6" NUMBER(23,6),

        "KAR_UPDATE_DT" DATE,

        "KAR_CREATE_DT" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING

  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "TS_KAR_HIST_DATA";

Export the table data only from source database:

exp USERNAME TABLES=LMSOGS.BB_CONNECTION_HISTORY_12AUG GRANTS=N INDEXES=N ROWS=Y TRIGGERS=N CONTRAINTS=N IGNORE=Y FILE=BB_CONNECTION_HISTORY_12AUG.dmp LOG=exp_BB_CONNECTION_HISTORY_12AUG.log;

SCP the dump file to destination database:

Import the table to Destination database Schema:

imp LMSOGS/********* FROMUSER=LMSOGS TOUSER=LMSOGS TABLES=BB_CONNECTION_HISTORY_12AUG GRANTS=N INDEXES=N rows=y  CONSTRAINTS=N IGNORE=Y FILE=BB_CONNECTION_HISTORY_12AUG.dmp LOG=imp_BB_CONNECTION_HISTORY_12AUG.log

Create Index manually or do structure export import. Create primary key. referential constraints etc as per source.

Re-synchronization of the Recovery Catalog

                  Re-synchronization of the Recovery Catalog 

Changes made to the target database aren't automatically propagated to recovery catalog. "Backup" and "Copy" commands automatically perform a re-synchronization each time they run.

Manual re-synchronization required in following circumstances:-

1) When target database has just undergone a number of physical changes 

2) When the target database is performing a very large number of log switches in-between the              backups. 

Re-sync. Operation:-

RMAN reads the target database's control file to update information it keeps regarding datafiles, log switches, physical schema etc.

RMAN> RESYNC CATALOG;

Navigating in vi editor-AIX

                                             Navigating in VI Editor-AIX

As an Oracle DBA you need to be aware of some operating system chores. You need to know how to move around a file using VI editor.
In command mode, your keyboard becomes an interface tool, as opposed to a text-input tool.The first basic operation to learn is moving the cursor. Most modern versions of vi allow you to use the arrow keys on the keyboard, but vi users prefer the easily accessible keys under their fingertips, h-j-k-l:
·         h and l represent left and right, respectively, which is intuitive because they bound the four navigation keys on the left and on the right.
·         k moves the cursor up.
·         j moves the cursor down.

You can also navigate to the end of each word by using the e key to move forward, or by pressing g. Press e to go backward
Now, you can quickly move within a line but, as you deal with larger files, it's also important to rapidly navigate from line to line. There are a few ways to do this in vi. You can use the up and down commands (k and j), or you can use the page-up and page-down commands. Most commands in vi don't require you to press the Ctrl key, but the page-up and page-down commands are a couple of exceptions to this loose rule:
·         Press Ctrl-u to go up a page.
·         Press Ctrl-d to go down a page.

To quickly navigate to the beginning or end of a file, you can use gg or G:
·         Pressing gg puts the cursor on the first line of the document.
·         Pressing G puts the cursor on the last line of the document.
Press gg, i, and then type new text. Press the Esc key when you're finished to go back to command mode. Remember, you must go back into command mode after entering new text, or there is no way to navigate the document.

Steps for generating the PGP public key-Oracle

               Steps  for generating the PGP public key

Client's Request was:

Please generate the PGP public on 1.199.***.16 UAT server from  oracle user id as we need to run the  pgp  command via PL Sql.

Steps  for generating the PGP public key 

·         Login on 1.199.***.16 sever via oracle id

·         Execute the following commands

o   Cd /sbiods/dm/PGP/PGP1/pgp-6.5.8

o    pgp –kg

Drop Recovery Catalog-RMAN-Oracle Database

                            Drop the Recovery Catalog

To drop a Recovery Catalog twice run following command from RMAN:

RMAN> DROP CATALOG;

RMAN> DROP CATALOG;

Recovery Catalog setup-RMAN-Oracle Database

                  Setup Your Recovery Catalog Database

Step - 1: Create Recovery Catalog Schema:

SQL> create user RMAN identified by rman

           temporary tablespace temp

           default tablespace rman_tablespace

           quota unlimited on rman_tablespace

Default tablespace may be any existing one or we can create a new one before creating recovery catalog schema.

Step - 2: Grant necessary privileges to rman: 

SQL> grant RECOVERY_CATALOG_OWNER to RMAN;

Step - 3: Connect to RMAN:

$rman CATALOG rman/rman@catalogdbname target targetdbname

Step - 4: Create the Recovery Catalog:

RMAN> CREATE CATALOG;

Step - 5: Registering a Database:

RMAN> REGISTER DATABASE;

Remember to register a new database in the recovery catalog, first we need to connect to the target database. Repeat step 5 after connecting to the particular target database for each database you want to register.

RMAN> REPORT SCHEMA

To make sure all the datafiles of target database are showing up in the list.

Switch Over-Role Transition-Oracle Dataguard

              Switch Over-Role Transition-Oracle Dataguard

Switch over is planned role transition of Oracle dataguard. It's used for production planned maintenance task etc.

Step-1:

Verify there are no redo transport error or redo gap at standby database.

SQL> select STATUS,GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID=2;

STATUS                                               GAP_STATUS

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

VALID                                                    NO GAP

Step-2:

Verify that target standby database is ready for switch over:

Let 

PRIMARY => DB_UNIQUE_NAME=>PRIMARY

PHYSICAL STANDBY=>DB_UNIQUE_NAME=>STNDBY

SQL> alter database switchover to stndby verify;

(Redo apply must be running before switchover operation)

Step-3:

Initiate Switch Over:

SQL> alter database switchover to standby;

Step-4:

Open the new primary database;

SQL> alter database open;

Step-5:

Mount the new physical standby database:

SQL> startup mount;

Step-6:

Start redo apply on the physical standby database:

alter database recover managed standby database disconnect from session;

Failover-Role Transition-Oracle DataGuard-Manual Steps

   Failover-Role Transition-Oracle DataGuard-Manual Steps

Oracle Dataguard is part of Oracle High Availability Solution and widely used for disaster recovery of oracle database. There are two types of role transition available with oracle dataguard. One is Switch Over and another is Fail Over. Failover is basically unplanned role transition while Fast Start Failover is enabled. But if First Start Failover is not enabled then automatic failover is not possible. You can use following steps for manual failover. This is applicable for physical standby database.

Step-1:

On Primary database:-

Flush all primary database currently generated redo data to standby.

SQL> alter system flush redo to target_db_name(standby);

Step-2:

Verify that primary and standby database log sequence is same that is all redo sent from primary database are applied to standby database.

SQL> select unique thread# as thread,MAX(SEQUENCE#) over (PARTITION BY THREAD)            as LAST from V$ARCHIVED_LOG;

Check for gap.

Step-3:

Stop redo apply on standby database:

SQL> alter database recover managed standby database cancel;

Step-4:

Switch the physical standby database to primary role:

SQL> alter database failover to standby;

Step-5:

Open new primary database;

Step-6:

Backup the new primary database.

Performance Tuning Oracle Database

Following script will help you getting overall Oracle database Performance in one go:

set serveroutput on
declare
cursor c1 is select version
from v$instance;
cursor c2 is
    select
          host_name
       ,  instance_name
       ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime
       ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime
     from v$instance;
cursor c4 is
select * from (SELECT count(*) cnt, substr(event,1,50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC) where rownum <6;
cursor c5 is
select round(sum(value)/1048576) as sgasize from v$sga;
cursor c6 is select round(sum(bytes)/1048576) as dbsize
from v$datafile;
cursor c7 is select 'top physical i/o process' category, sid,
       username, total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('physical reads', 'physical writes',
                     'physical reads direct',
                     'physical reads direct (lob)',
                     'physical writes direct',
                     'physical writes direct (lob)')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('physical reads', 'physical writes',
                       'physical reads direct',
                       'physical reads direct (lob)',
                       'physical writes direct',
                       'physical writes direct (lob)'))
where rownum < 2
union all
select 'top logical i/o process', sid, username,
       total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
 and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid,
       username, total_user_mem,
       round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_mem
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('session pga memory', 'session uga memory')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_mem
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username,
       total_user_cpu,
       round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session'
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session')
where rownum < 2;

cursor c8 is select username, sum(VALUE/100) cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and username is not null
and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by username
order by 2 desc;
begin
dbms_output.put_line ('Database Version');
dbms_output.put_line ('-----------------');
for rec in c1
loop
dbms_output.put_line(rec.version);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Hostname');
dbms_output.put_line ('----------');
for rec in c2
loop
     dbms_output.put_line(rec.host_name);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('SGA Size (MB)');
dbms_output.put_line ('-------------');
for rec in c5
loop
     dbms_output.put_line(rec.sgasize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Database Size (MB)');
dbms_output.put_line ('-----------------');
for rec in c6
loop
     dbms_output.put_line(rec.dbsize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Instance start-up time');
dbms_output.put_line ('-----------------------');
for rec in c2 loop
 dbms_output.put_line( rec.starttime );
  end loop;
dbms_output.put_line( chr(13) );
  for b in
    (select total, active, inactive, system, killed
    from
       (select count(*) total from v$session)
     , (select count(*) system from v$session where username is null)
     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)

     , (select count(*) inactive from v$session where status = 'INACTIVE')
     , (select count(*) killed from v$session where status = 'KILLED')) loop
dbms_output.put_line('Active Sessions');
dbms_output.put_line ('---------------');
dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');
  end loop;
  dbms_output.put_line( chr(13) );
 dbms_output.put_line( 'Sessions Waiting' );
  dbms_output.put_line( chr(13) );
dbms_output.put_line('Count      Event Name');
dbms_output.put_line('-----      -----------------------------------------------------');
for rec in c4
loop
dbms_output.put_line(rec.cnt||'          '||rec.event);
end loop;
dbms_output.put_line( chr(13) );

dbms_output.put_line('-----      -----------------------------------------------------');

dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');
dbms_output.put_line ('---------------');
for rec in c7
loop
dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);
end loop;

dbms_output.put_line('------------------------------------------------------------------');

dbms_output.put_line('TOP CPU users by usage');
dbms_output.put_line ('---------------');
for rec in c8
loop

dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);
dbms_output.put_line ('---------------');
end loop;

end;
/