Monday 24 December 2012

Steps To Enable Auditing on RAC Database

Auditing has two types, there are
  • standard - auditing based on statement, privilege and object-level
  • fine-grained - auditing, which is based on content
Standard auditng has 3 levels.

  • statement - audit all actions on any type of object
  • privilege - audit actions that stem from system privileges
  • object-level - specific audit actions like select, update, insert or delete

  • Auditing depends on which value we pass on audit_trail

    the values are..
    • none - disables auditing
    • os - records the information to a o/s file (uses parameter audit_file_dest for the file location)
    • db - records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ ) to display audit information.
    • db, extended - as per the db value but also populates the sqlbind and sqltext clob columns
    • xml - audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
    • xml,extended - as per xml option but also populates the sqlbind and sqltext clob columnstandard
    For all 3 levels of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session).

    Steps to enable auditing ...   Ex  11gR2 2 Node RAC setup.

    We had to drop old audit (aud$) table, for that we need to keep our database in restricted mode.

    Steps:

    srvctl stop listener -n nodename1
    srvctl stop listener -n nodename2

    sqlplus>
    alter system set cluster_database=false scope=spfile

    alter system set audit_trail=db,extended scope=spfile

    $srvctl stop database -d dbname -o immediate

    >startup restrict
    >truncate table aud$
    >alter system set cluster_database=true scope=spfile
    >shu immediate;

    $srvctl start database -d dbname

    $srvctl start listener -n nodename1

    $srvctl start listener -n nodename2

    create users

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

    AUDIT ALL BY username BY ACCESS;

    AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;

    AUDIT EXECUTE PROCEDURE BY username BY ACCESS;
     

    Monday 17 December 2012

    opidcl aborting process unknown ospid (27181) as a result of ORA-2396

    If you face the above error,

    You can safely ignore.

    It was written in alertlog because of timed out sessions. If you dont want this in alertlog you may have to reset your idle time to unlimited which is not possible in huge environment. So it is better to ignore the above error message.

    Thanks.

    Experts comments welcome.

    Error 0 in kwqmnpartition-update_aqs_instance, aborting txn

    If you face above error in your alert log file,

                    It would be because of some sys invalid objects. It would end up with error when end users access something through application.

                    To rectify this first we have to run utlrp.sql script which is located in ORACLE_HOME/rdbms/admin path.

    If that doesn't work, we have to take downtime and disable all the triggers then run catproc.sql script which is also in the same location.

    Thanks. 

    Wednesday 10 October 2012

    CRS is not coming up.. hitting a bug

    RAC - Windows server 2003 - CR Service is not coming up. - On 2 node RAC one node is down.

    When try start CR service on node 2 (which is down) Both nodes are down with Blue screen error(OS error)

    10.2.0.1

    When I check OCR logs..

    2012-10-09 21:54:46.058: [ CRSMAIN][632]32Initializing OCR
    2012-10-09 21:54:46.073: [ OCROSD][632]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.

    2012-10-09 21:54:46.073: [ OCROSD][632]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.
    2012-10-09 21:54:46.073: [ OCRRAW][632]proprioo: for disk 0 (E:\cdata\crs\data.ocr), id match (1), my id set (1830547516,1028247821) total id sets (1), 1st set (1830547516,1028247821), 2nd set (0,0) my votes (2), total votes (2)
    2012-10-09 21:54:46.089: [ OCRMAS][2872]th_master:12: I AM THE NEW OCR MASTER at incar 1. Node Number = 1
    2012-10-09 21:54:46.089: [ OCROSD][2872]utgdv:11:could not read reg value ocrmirrorconfig_loc os error= The system could not find the environment option that was entered.

    I had a doubt it would not able to access OCR.loc So get into ocr file on both nodes and check whether it is accessible or not.

    To find the locaton u can use ocrcheck command..

    or on windows ---> run ---->regedit ----> HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\OCR

    Ocr.loc  In my enviroment  E:\cdata\crs\data.ocr in this format and it was accessible.

    To check votedisk location You can use..     crsctl query css votedisk

    Checked--- All ip's were pinging --- no network related issue.

    When checking css logs, We got to know that It was 10.2.0.1 bug. We were hitting a bug.

    Log for reference
    StartCMMon(): clssnmNMDetach failed - 2 10.2.0.1

    Bug 4714940: CLSSGMSTARTNMMON: TIMED OUT WAITING ON NESTED NM RECONFIG. SELF-SACRIFICING
    -> duplicate of
    Bug 4682525: STARTCMMON(): CLSSNMNMDETACH FAILED, IN CSS
    -> duplicate of
    Bug 4682514: CLSSGMSLAVECMSYNC: RECONFIG TIMEOUT, IN CSS
    -> fixed by 10202
    To fix this bug minimum required patchset is 10.2.0.2 we decided to go for 10.2.0.5 which is the latest.
    Patch # 4547817 10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
    Patch # 7213942 ORACLE 10.2.0.2 PATCH 18 BUG FOR WINDOWS-64 AMD64 AND INTEL EM64T XP AND 2003 (latest minipatch over 10202)

    patch # 8202632 10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
    patch # 14408636 ORACLE 10G 10.2.0.5 PATCH 18 BUG FOR WINDOWS (64-BIT AMD64 AND INTEL EM64) (latest minipatch over 10205)

    Thanks.....





     

    Tuesday 11 September 2012

    ERROR : Connection was refused with error ORA-12519

    Please check MOS id:552765.1


    One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.

    In this case please check v$resource_limt view to find maximum utilization and available utilization.

    For this,


    Select resource_name,current_utilization,max_utilization ,limit_value from v$resource_limit;

    If current utilization reaches the limit then increase the appropriate value, then if necessary bounce back the database.

    In my case it processes limit has been exceeded,  so i increase both nodes and restarted the RAC DB.

    Check lsnrctl status,

    It may show status is blocked.

    In my case;

    On both nodes ---
    SQL> alter system set sessions=800 scope=spfile;

    Sistema modificado.

    SQL> alter system set processes=1000 scope=spfile;


    shutdown immediate
    startup ------------  using svctl

    processes integer 1000
    sessions integer 1500
    Because sessions are derived from processes.. Keep in mind...

    ASM insufficient Privileges and while starting syntax error or unresolved network name 'LISTENER_+ASM'

    For the above error,

    as well as [oracle@racdr ~]$ sqlplus
    SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 8 13:00:14 2012
    Copyright (c) 1982, 2009, Oracle. All rights reserved.
    Enter user-name: sys as sysdba
    Enter password:
    Connected to an idle instance.
    SQL> startup mount pfile ='/u01/app/oracle/diag/asm/+asm/+ASM/trace/init+ASM.ora';
    ORA-01031: insufficient privileges
    SQL> conn sys as sysasm
    Enter password:
    ERROR:
    ORA-01031: insufficient privileges

    We  need to export all the home paths corectly

    For ex.. while starting ASM.(11g)

    ORACLE HOME should be grid home and also Grid home should be grid home..
    export ORACLE_SID as +ASM


    If you have any parameter like sqlnet.authentication_services inside your sqlnet.ora file comment that one. And also comment the LOCAL_LISTENER 'LISTENER_+ASM' (if exist ) in the pfile.

    Better U create a pfile using ASM alertlog File

    Following Steps has to be done.

     $export ORACLE_SID=+ASM
    $export ORACLE_HOME=<Your Grid Oracle Home>
    $export PATH=$PATH:$ORACLE_HOME/bin

    Create the pfile with below values under $ORACLE_HOME/dbs

    $cd $ORACLE_HOME/dbs
    $vi init+ASM.ora

    large_pool_size = 12M
    instance_type = "asm"
    remote_login_passwordfile= "EXCLUSIVE"
    asm_power_limit = 1
    diagnostic_dest = "/u01/app/oracle"
    asm_diskstring='/dev/oracleasm/disks/*'

    $sqlplus "/as sysasm"

    sql>startup pfile='?/dbs/init+ASM.ora';

    sql>alter diskgroup data mount; (Mention ur diskgroup name)


    To catch details of ur ASM

    You can use following commands for Linux.
    # rpm -qa |grep oracleasm
    # /etc/init.d/oracleasm status
    # lsmod |grep -i asm
    # cat /proc/filesystems |grep -i asm
    # ls -l /usr/sbin/oracleasm-discover
    # /etc/init.d/oracleasm listdisks
    $ /etc/init.d/oracleasm listdisks | xargs /etc/init.d/oracleasm querydisk -p
    # ls -l /dev/oracleasm/disks/*
    # cat /proc/partitions
    # cat /etc/sysconfig/oracleasm
    # ls -l /etc/sysconfig/oracleasm*
    Ur comments are welcome


     

    Monday 10 September 2012

    Migration of 10.2.0.3(32-Bit) database to 11.2.0.3(64-Bit)

    Test Setup

    Initial Platform : Redhat Linux 5 update 6 (32 – bit)

    Since Oracle DB 10.2.0.3 is not a complete patch set, we need to install the base version that is 10.2.0.1 and create a test database ( here RMS), later upgrade it into 10.2.0.3 for test setup.

    Before installing 10g, do the prerequisites, and also install all the required rpms which you can find inside RHEL media Server directory.

    After the Successful installation of Oracle 10g & creation of test database,

    Download the patchset for 10.2.0.3 and unzip the same.

    Before installing the patchset.

    1.Stutdown the database.
    2.Stop the listener >> lsnrctl stop
    3.stop dbconsole >> emctl stop dbconsole
    4.stop isqlplusctl >> isqlplusctl stop
    [oracle@localhost ~]$ . .bash_profile

    [oracle@localhost ~]$ emctl status dbconsole

    TZ set to Asia/Calcutta
    Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 
    Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
    Oracle Enterprise Manager 10g is running.


    Logs are generated in directory /home/oracle/oracle/product/10.2.0/db_1/localhost.localdomain_rms/sysman/log

    [oracle@localhost ~]$ emctl stop dbconsole
    TZ set to Asia/Calcutta
    Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 
    Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
    Stopping Oracle Enterprise Manager 10g Database Control ...
    ... Stopped.

    [oracle@localhost ~]$ emctl status dbconsole
    TZ set to Asia/Calcutta
    Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 
    Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
    http://localhost.localdomain:1158/em/console/aboutApplication

    Oracle Enterprise Manager 10g is not running.

    Unzip the Patch set for 10.2.0.3 u can get Disk1 directory.

    Install the patch set in the existing path, where 10.2.0.1 binaries installed. It will automatically update the binaries. Or else you can use Opatch utility.

    [oracle@localhost ~]$ cd Disk1/

    [oracle@localhost Disk1]$ ./runInstaller

    Starting Oracle Universal Installer...
    Checking installer requirements...
    Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
    Passed
    All installer requirements met
    Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-08-20_05-33-38AM. Please wait ...[oracle@localhost Disk1]$ Oracle Universal Installer, Version 10.2.0.3.0 Production
    Copyright (C) 1999, 2006, Oracle. All rights reserved.
    Asia/Calcutta
    [oracle@localhost Disk1]$
    For Ex.. Updation of binaries process..

    Updating jar file "/home/oracle/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_jobs/_jobsList.class"

    Updating jar file "/home/oracle/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_jobs/_jobsList$__jsp_StaticText.class"

    After the successful upgradation of binaries, You can start the database with upgrade mode.

    Or you can upgrade using dbua utility.

    sql> startup upgrade;

    Run Catupgrd.sql

    @?/rdbms/admin/catupgrd.sql

    Oracle Database Server VALID 10.2.0.3.0 00:08:09
    JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:03:05
    Oracle XDK VALID 10.2.0.3.0 00:00:41
    Oracle Database Java Packages VALID 10.2.0.3.0 00:00:48
    Oracle Text VALID 10.2.0.3.0 00:00:26
    Oracle XML Database VALID 10.2.0.3.0 00:01:38
    Oracle Data Mining VALID 10.2.0.3.0 00:00:22
    Oracle Rule Manager VALID 10.2.0.3.0 00:00:12

    .

    Total Upgrade Time: 00:25:08

    DOC>#######################################################################
    DOC>#######################################################################

    Bounceback the database with normal mode then run utlrp.sql script to validate invalid objects.

    SQL> shu immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    Startup;

    SQL> select count(*) from dba_objects where status = 'INVALID';

    COUNT(*)

    506

    SQL> @?/rdbms/admin/utlrp.sql

    TIMESTAMP
    DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
    DOC>
     
    SQL> select count(*) from dba_objects where status = 'INVALID';

    COUNT(*)

    0

    Check the status and version of all components

    SQL> select comp_name , status ,version from dba_registry;

    COMP_NAME STATUS VERSION

    Oracle Database Catalog Views VALID 10.2.0.3.0
    Oracle Database Packages and Types VALID 10.2.0.3.0
    Oracle Workspace Manager VALID 10.2.0.1.0
    Server JAVA Virtual Machine VALID 10.2.0.3.0
    Oracle XDK VALID 10.2.0.3.0
    Oracle Database Java Packages VALID 10.2.0.3.0
    Oracle Expression Filter VALID 10.2.0.3.0
    Oracle Data Mining VALID 10.2.0.3.0
    Oracle Text VALID 10.2.0.3.0
    Oracle XML Database VALID 10.2.0.3.0
    Oracle Rule Manager VALID 10.2.0.3.0
    Oracle interMedia VALID 10.2.0.3.0
    OLAP Analytic Workspace VALID 10.2.0.3.0
    Oracle OLAP API VALID 10.2.0.3.0
    OLAP Catalog VALID 10.2.0.3.0
    Spatial VALID 10.2.0.3.0
    Oracle Enterprise Manager VALID 10.2.0.3.0

    Everything is fine now..

    Download Oracle DB 11.2.0.3 and install it in a different path
    After Installing 11.2.0.3.. Check the prerequisites to upgrade to 11g for the existing 10g database.

    Inside 11g ORACLE_HOME/rdbms/admin path you can find utlu112i.sql script copy it into temporary location and run it on 10g database to check the prerequisites.
    [oracle@localhost ~]$ sqlplus
    SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 21 05:43:27 2012

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
    Enter user-name: sys as sysdba
    Enter password:
    Connected to:

    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

    With the Partitioning, OLAP and Data Mining options

    SQL> @/tmp/utlu112i.sql

    Oracle Database 11.2 Pre-Upgrade Information Tool 08-21-2012 05:44:01
    Script Version: 11.2.0.3.0 Build: 001
    **********************************************************************
    Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
    Components: [The following database components will be upgraded or installed]
    **********************************************************************

    --> Oracle Catalog Views [upgrade] VALID
    --> Oracle Packages and Types [upgrade] INVALID
     
    Changes will need to be made in the init.ora or spfile. Check the timezone

    SQL> select count(*) from dba_objects where status='INVALID';

    632

    SQL> select * from v$timezone_file;

    timezlrg.dat 3

    Before Upgrade Purge the recycle bin and also gather the statistics for all schemas.

    SQL> purge dba_recyclebin;

    Upgradation using DBUA

    Export the Oracle 11g Home path in the bash Profile.
    In 11g home invoke dbua...

    1.select the database which needed to upgrade..


    I:\Screenshot-4.png

I:\Screenshot-4.png



    (For our test DB RMS has been selected)

    2.Select the timezone too.
    4.Select the degree of parallelism


    I:\Screenshot-3.png

I:\Screenshot-3.png


    It will take long time to complete.


    I:\Screenshot-6.png

I:\Screenshot-6.png


    Everything will be upgraded automaticallically, when you use dbua including timezone.


    I:\Screenshot-7.png

I:\Screenshot-7.png



    Export 11g Home and check the status of all components.

    SQL> col comp_name for a40;

    SQL> /

    COMP_NAME STATUS VERSION
     
    Oracle Enterprise Manager VALID 11.2.0.3.0
    OLAP Catalog VALID 11.2.0.3.0
    Spatial VALID 11.2.0.3.0
    Oracle Multimedia VALID 11.2.0.3.0
    Oracle XML Database VALID 11.2.0.3.0
    Oracle Text VALID 11.2.0.3.0
    Oracle Data Mining VALID 11.2.0.3.0
    Oracle Expression Filter VALID 11.2.0.3.0
    Oracle Rule Manager VALID 11.2.0.3.0
    Oracle Workspace Manager VALID 11.2.0.3.0
    Oracle Database Catalog Views VALID 11.2.0.3.0
    Oracle Database Packages and Types VALID 11.2.0.3.0
    JServer JAVA Virtual Machine VALID 11.2.0.3.0
    Oracle XDK VALID 11.2.0.3.0
    Oracle Database Java Packages VALID 11.2.0.3.0
    OLAP Analytic Workspace VALID 11.2.0.3.0
    Oracle OLAP API VALID 11.2.0.3.0
    17 rows selected.

    SQL> select * from v$timezone_file;

    FILENAME VERSION
    -------------------- ----------
    timezlrg_14.dat 14

    We have upgraded the database from 10.2.0.3(32-Bit) to 11.2.0.3(32-Bit).

    Now, We have to migrate this database to 64-bit

     
    1. Install 64-bit RHEL 5.6 and get the server ready.

    2. Do the prerequisites before install 11g like kernel settings

    3. Install all the required rpms.( u can find inside server directory of RHEL Media)

    4. Create oracle user under dba group

    5. Give the appropriate privileges to Oracle user, in which software has to be installed.

    6. Install Oracle 11.2.0.3 64-Bit Software.

     
    Clone the database from 32-bit server to 64 – Bit Server by creating appropriate directory.

    (Using SCP copy all the data, control , redo and spfiles to 64- bit Server)

    (Comment audit_trail_file parameter in pfile and create spfile by the same)

    In the New Server After the Cloning,(Export Bash profile)
    Startup Upgrade;
    Run
    @?/rdbms/admin/utlirp.sql -------

    PL/SQL procedure successfully completed.

    DOC>#######################################################################

    SQL> select count(*) from dba_objects where status='INVALID';
    COUNT(*)
    8841

    Normal Shutdown

    Shutdown;

    startup;

    SQL> @?/rdbms/admin/utlrp.sql
    2 threads pls_integer := &&1;
    3 BEGIN
    4 utl_recomp.recomp_parallel(threads);
    5 END;

    [oracle@localhost ~]$ sqlplus

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 23 01:58:50 2012

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

    Enter user-name: sys as sysdba
    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> select count(*) from dba_objects where status ='INVALID';

    COUNT(*)
     
    0

    SQL> select platform_name from v$database;

    PLATFORM_NAME
    --------------------------------------------------------------------------------
    Linux x86 64-bit

    SQL> select comp_name ,version,status from dba_registry;

    COMP_NAME VERSION STATUS

    ------------------------------ -----------
    Oracle Enterprise Manager 11.2.0.3.0 VALID
    OLAP Catalog 11.2.0.3.0 VALID
    Spatial 11.2.0.3.0 VALID
    Oracle Multimedia 11.2.0.3.0 VALID
    Oracle XML Database 11.2.0.3.0 VALID
    Oracle Text 11.2.0.3.0 VALID
    Oracle Data Mining 11.2.0.3.0 VALID
    Oracle Expression Filter 11.2.0.3.0 VALID
    Oracle Rule Manager 11.2.0.3.0 VALID
    Oracle Workspace Manager11.2.0.3.0 VALID
    Oracle Database Catalog Views11.2.0.3.0 VALID
    Oracle Database Packages and Types11.2.0.3.0 VALID
    JServer JAVA Virtual Machine11.2.0.3.0 VALID
    Oracle XDK 11.2.0.3.0 VALID
    Oracle Database Java Packages11.2.0.3.0 VALID
    OLAP Analytic Workspace 11.2.0.3.0 VALID
    Oracle OLAP API 11.2.0.3.0 VALID
    17 rows selected.
    SQL> select ( case length(address) when 16 then '64 - bit oracle'
    2 when 8 then '32-bit'
    3 when 32 then '128-bit' end) wordsize
    4 from v$sql
    5 where rownum < 2;

    WORDSIZE

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

    64 - bit oracle

    Unix Server Performance related to RAM - 32 Bit server

    As every one knows top is the best command to calculate the Performance in Unix.

    Here I have an issue, Where in I have to prove that the added RAM increased the performance and also th server is using that RAM.

    As understood from experts points, Normally 32 bit server only address upto 4GB RAM.
    But in my case it is utilizing the whole 8GB. But I am unable to increase my kernel.shmmax parameter value. If so not able to start ORACLE Database.

    But This RAM Upgradation is helpfull in some ways,

    For Example it is not going fo SWAP Space.

    [oracle9i@dbserver oracle9i]$ free -g
                 total       used       free     shared    buffers     cached
    Mem:             7          7          0          0          0          7
    -/+ buffers/cache:          0          7
    Swap:            5          0          5


    [oracle9i@dbserver oracle9i]$ uname -i
    i386

    sar -r gives,



    07:20:00 AM kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
    07:30:00 AM     25816   8221028     99.69         0    258648   7485636   6143992         0      0.00
    07:40:00 AM     25656   8221188     99.69         0    258944   7485816   6143992         0      0.00
    07:50:00 AM     22484   8224360     99.73         0    258868   7486748   6143992         0      0.00
    08:00:00 AM     17924   8228920     99.78         0    243440   7483468   6143992         0      0.00
    08:10:00 AM     18116   8228728     99.78         0    211104   7463288   6143992         0      0.00
    08:20:00 AM     24196   8222648     99.71         0    122844   7497572   6143992         0      0.00
    08:30:00 AM     19576   8227268     99.76         0    103964   7512028   6143992         0      0.00
    08:40:00 AM     19284   8227560     99.77         0     95216   7503028   6143992         0      0.00
    08:50:00 AM     23900   8222944     99.71         0     82900   7475676   6143992         0      0.00
    09:00:00 AM     19792   8227052     99.76         0     85444   7564408   6143992         0      0.00
    09:10:00 AM     20416   8226428     99.75         0     89540   7554876   6143992         0      0.00
    09:20:00 AM     18792   8228052     99.77         0     85996   7547380   6143992         0      0.00
    09:30:00 AM     18192   8228652     99.78         0     82276   7535540   6143992         0      0.00
    09:40:00 AM     20844   8226000     99.75         0      6712   7600744   6143992         0      0.00
    09:50:00 AM     25244   8221600     99.69         0     12392   7597332   6143992         0      0.00
    10:00:00 AM     19828   8227016     99.76         0     17836   7593152   6143992         0      0.00
    10:10:00 AM     18460   8228384     99.78         0     22264   7581268   6143992         0      0.00
    10:20:00 AM     19432   8227412     99.76         0     27408   7564876   6143992         0      0.00
    10:30:00 AM     21004   8225840     99.75         0     32144   7546428   6143992         0      0.00
    10:40:00 AM     27180   8219664     99.67         0     36924   7525764   6143992         0      0.00
    10:50:00 AM     23308   8223536     99.72         0     41744   7540536   6143992         0      0.00
    11:00:00 AM     19848   8226996     99.76         0     40200   7527148   6143992         0      0.00
    Average:    kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
    Average:        23443   8223401     99.72         0    157658   7632032   6143992         0      0.00

    So I hope it is utilizing whole 8GB.

    SAR will record always 1 week data. If not we need to configure.

    For Example to get 3rd of this Month ,exactly 1 week back..(Only ram if u use -r)

    [oracle9i@dbserver oracle9i]$ sar -f /var/log/sa/sa03 -r

    10:00:00 PM kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
    10:10:00 PM   5957580   2289264     27.76         0    305448   1546828   6143992         0      0.00
    10:20:00 PM   5960008   2286836     27.73         0    305492   1546832   6143992         0      0.00
    10:30:00 PM   5959456   2287388     27.74         0    305548   1546836   6143992         0      0.00
    10:40:00 PM   5959124   2287720     27.74         0    305604   1546836   6143992         0      0.00
    10:50:00 PM   5959976   2286868     27.73         0    305664   1546840   6143992         0      0.00
    11:00:00 PM   5959284   2287560     27.74         0    305732   1546840   6143992         0      0.00
    11:10:00 PM   5958644   2288200     27.75         0    305792   1546844   6143992         0      0.00
    11:20:00 PM   5957224   2289620     27.76         0    305844   1546844   6143992         0      0.00
    11:30:00 PM   5957964   2288880     27.75         0    305912   1546848   6143992         0      0.00
    11:40:00 PM   5958492   2288352     27.75         0    305948   1546848   6143992         0      0.00
    11:50:00 PM   5958352   2288492     27.75         0    305984   1546852   6143992         0      0.00
    Average:      5963314   2283530     27.69         0    301578   1546674   6143992         0      0.00


    Previously,

    11:00:00 AM kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
    11:10:00 AM     23880   4090356     99.42         0     69548   3563356   6071340     72636      1.18
    11:20:00 AM     26304   4087932     99.36         0     69492   3559892   6071340     72636      1.18
    Average:        25111   4089125     99.39         0     57561   3567780   6072109     71867      1.17


    So I feel 32 bit server addresses 8gb RAM and also it is utilizing the whole space.

    Need inputs from experts.. Thanks.

    Friday 3 August 2012

    RAC Server Time Change

    Hi Friends,

    I would like to share the activity that i was done last week.

    In RAC server both the server timings were behind 20 minutes of IST.

    I had to change, I thought it was a big activity if anything goes wrong server would crash down.

    But it was very simple, its not complecated as changing the TIMEZONE.

    You can find the below steps to do this on Linux operating system.
    Login as Oracle user

    a) First shut the enterprise manager $ emctl stop dbconsole ( do this on both nodes)
    b) Shutdown the database $ srvctl stop database -d db_name                                
    c) Stop nodeapps ( do this on both nodes) $ srvctl stop nodeapps -n node
    Login as root
    Stop ASM and CRS ( do this on both nodes)
    # crsctl stop cluster -all   
         
    Change the time
    login as root ( do this on both the nodes)
    Example # date –s "3 JUN 2012 20:00:00"

    Reboot both the Nodes

    Issues Faced:

    Since it is 11g you no need to stop nodeapps and ASM. stop cluster -all would stop all one by one

    CRS was coming down properly, So  I used crsctl stop crs -f . With force i was able to down the CRS.

    Keep some 5 seconds difference between both nodes, secondary should be lagging.

    Thanks.

    Valuable comments are welcome.