Pages

Wednesday 28 March 2012

Buffer Cache Hit Ratio goes down to Negative values


Today I faced an interesting issue in my production database. Daily we check the Buffer Cache Hit Ratio of the database. Today I got the negative value for the hit ratio.
I am using the below formula for calculating the buffer cache hit ratio.

                                               PHYSICAL_READS
               1  -   ------------------------------------------------------
                                DB_BLOCK_GETS + CONSISTENT_GETS

               
I tried with different formulae that I got from the google. But it is of no use. The I searched it in the metalink and I got the answer in it. It was a bug 2645071 stated in the 'Oracle Database Performance Tuning Guide 10g Release 1 (10.1)'  and found the  correct formula to calculate the Buffer Cache Hit Ratio

                                      'physical reads cache'
             1 - -----------------------------------------------------------------------------
                       'consistent gets from cache' + 'db block gets from cache'

.
Where,
physical reads cache’=’physical reads’-‘physical reads direct’-‘physical reads direct(lob)’
‘consistent gets from cache’= ‘consistent gets’
‘db block gets from cache’= ‘db block gets’

physical reads direct:
  This Oracle metric is the number of reads directly from the disk bypassing the buffer cache. For example if there is any request for large data, data-intensive operations such as parallel query, reads of the disk blocks bypass the buffer cache to maximize the transfer rates.
High disk reads indicates the small db buffer cache size.

Tuesday 27 March 2012


ORA-00202: control file: ''
ORA-27036: translation error, unable to expand file name
ORA-205 signalled during: ALTER DATABASE   MOUNT...


I have an issue in my development database today. Suddenly client ping me and told that he cannot able to access the database.  He got the below error.




I checked the database is in archive log mode and the archive log destination is used 100%(Disk space). This database  was created using the RMAN duplication technique. To make the database in noarchive log mode I shutdown the database and tried to up database in mount state.

SQL>startup nomount;

ORA-205 signalled during: ALTER DATABASE   MOUNT...

SQL>select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
RCKDEV1         STARTED



I checked the alert_log file of the database. In that I observed that the control_files parameter is set to nothing. i.e.

Control_files=


And I observed the command ALTER SYSTEM SET control_files='' SCOPE=SPFILE; that was done by the previous DBA. And the database is using the omf feature(ORACLE MANAGED FILES)

The database is using spfile. So, I created a pfile from the spfile:

SQL>create pfile from spfile;


Added the control­_files parameter in the pfile and started the database.


control_files=’/u02/ RCKDEV1/controlfile/o1_mf_7mpsghnh_.ctl’,’ /u01/ RCKDEV1
                           /controlfile/o1_mf_7mpsghln_.c tl’

SQL>startup mount;
SQL>alter database noarchivelog;
SQL>alter database open;
SQL>select  instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
RCKDEV1         OPEN





Insatallation of Cassandra database

Note: To Install the cassandra database, the server should be installed with JDK 1.6 or later version.

1.      Download the cassandra software from the website http://cassandra.apache.org. It will be named as  apache-cassandra-x.x.x-bin.tar.gz, where x.x.x is the release of the product
  
2.        Unzip and untar the file using the below command.

#tar -zxvf apache-cassandra-1.0.8-src.tar.gz

3.       Create the below directories.

  * cd apache-cassandra-$VERSION
  * sudo mkdir -p /var/log/cassandra
  * sudo chown -R `whoami` /var/log/cassandra
  * sudo mkdir -p /var/lib/cassandra
  * sudo chown -R `whoami` /var/lib/cassandra
 
 
4.       Download the Apache ant software to build scripting language and Ivy plugin for dependancy management. The Apache ant software can be downloaded from the http://ant.apache.org website
  
5.      Extract the zip file as below.

#unzip apache-ant-1.8.3-bin.zip

  
6.      Download the Ivy plugin software from the http://ant.apache.org/ivy/download.cgi website.

7.       Extract the zip file.

#unzip apache-ivy-2.2.0-bin-with-deps.zip

               Note:There is no need to download the ivy software(6&7 steps) seperately.

8.      Navigate to apache-ant-1.8.3 and fire the below command.

   #cd apache-ant-1.8.3  
   #svn co https://svn.apache.org/repos/asf/ant/ivy/core/trunk ivy
               
The above step is building from the source, here we get the latest sources from the    subversions.
  
9.       Navigate to the Cassandra source directory (Cassandra home) i.e. /root/apache-cassandra-1.0.8-src in this example and fire the below command:
  
#cd /root/apache-cassandra-1.0.8-src


run the ant command which in the ant extracted software location-->bin directory from the Cassandra source directory

#/root/apache-ant-1.8.3/bin/ant (in this example)

   
 Because Ivy takes care of all the dependencies, it’s easy to build Cassandra once you have the source. Just make sure you’re in the root directory of your source download   and execute the ant program, which will look for a file called build.xml in the current directory and execute the default build target. Ant and Ivy take care of the rest. To execute the Ant program and start compiling the source, just type above command:
  
10.   At last you will see BUILD SUCCESSFUL message appeared on the screen and the location of the new property file which is similar to below.

createVersionPropFile:
     [propertyfile] Creating new property file: /root/apache-cassandra-1.0.8-src/build/classes/main/org/apache/cassandra/config/version.properties



11.   Now we can start the cassandra database using the below command from the CASSANDRA_HOME.

    #cd apache-cassandra-1.0.8-src/
    #bin/cassandra

                  In the end of the process, it is similar to the below message:
     INFO 14:38:02,250 Using synchronous/threadpool thrift server on localhost/127.0.0.1 : 9160
     INFO 14:38:02,254 Listening for thrift clients...


Now the cassandra is up and running

12.   Start the cassandra client.
#bin/cassandra-cli
   
     o/p:
Welcome to Cassandra CLI version 1.0.8-SNAPSHOT

Type 'help;' or '?' for help.  Type 'quit;' or 'exit;' to quit.
 [default@unknown]



    

    


Sunday 4 March 2012

Migrating file system database to ASM using RMAN




Ø  Create the database  in a regular file system with name SRSPASM(archivelog and spfile)

Ø  Prepare the database for migration

Before the database is migrated to ASM, the below database information should be collected.

·         Store the DBID:
If the database doesn’t have the recovery catalog, then the DBID may be used during the control file restore while migration(we need to set the DBID when we restore the controlfile).
                               
SQL>SELECT DBID FROM V$DATABASE;
DBID
----------
2839631796

·         Make a note of the datafiles:

Make a note of the datafiles which may use in the future if the database is to be reverted back to normal filesystem.

SQL>SELECT NAME FROM V$DATAFILE;
SQL>SELECT NAME FROM V$CONTROLFILES;


Ø  Switch the logfile.
SQL> ALTER SYSTEM SWITCH LOGFILE;
                  
Ø  Connect to rman and backup the database files to the ASM diskgroups.

[oracle@srspasm ~]$ rman target /
RMAN> BACKUP AS COPY DATABASE FORMAT='+dg1';
RMAN> BACKUP CURRENT CONTROLFILE FORMAT='/u01/oracle/srspasm/contbk.ctl';
RMAN>SHUT IMMEDIATE;

Note: It is better to take the backup when no users are connected to the database(Off hours). So there is no need to recover the database. If the backup is done during the working hours then the database has to be recovered after the migration.

Ø  Modify the server parameter file: change the control file parameter.


oracle@system8 ~]$ vi /u01/oracle/product/10.2.0/db_1/dbs/initsrspasm.ora

modify the control file

control_files='+DG1',’+FRA’


Ø  Shutdown the database, startup in nomount state and restore the controlfile.


[oracle@system8 ~]$ sqlplus / as sysdba
STARTUP NOMOUNT

CONNECT TO RMAN

RMAN> RESTORE CONTROLFILE FROM ‘/u01/oracle/srspasm/contbk.ctl';
RMAN> ALTER DATABASE MOUNT;
RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;



Ø  Datafiles and control files are in disk groups. Now convert log files and sp file and archive logs to disk groups.

·         spfile convertion:

SQL> CREATE SPFILE='+DG1' FROM PFILE;
[oracle@system8 dbs]$ vi initsrspasm.ora

remove all parameters and set
spfile='+DG1/srspasm/parameterfile/spfile.273.685995011'
[oracle@system8 dbs]$ rm spfilesrspasm.ora
SQL> shut immediate
SQL> startup
ORACLE instance started.
Database opened.

·         Convert  log files to disk groups:

         
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('+DG1') SIZE 10M, GROUP 4 ('+DG1') SIZE 10M,GROUP 5 ('+DG1') SIZE 10M;
DATABASE ALTERED.

Drop the groups which are in non asm diskgroups first make it inactive.

SQL>SELECT GROUP#,STATUS FROM V$LOG;

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
database altered.

SQL> SELECT MEMBER FROM V$LOGFILE;
MEMBER
-----------------------------------------------------------------------
+DG1/srspasm/onlinelog/group_3.274.686047325
+DG1/srspasm/onlinelog/group_4.275.686047325
+DG1/srspasm/onlinelog/group_5.276.686047325


·         convert archive logfiles to disk groups:

SQL> SHO   PARAMETER LOG_ARCHIVE_DEST_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u01/oracle/srspasm/a
                                                 rch
log_archive_dest_10                  string

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=+DG1';

System altered.

SQL> SHO    PARAMETER LOG_ARCHIVE_DEST_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=+DG1
log_archive_dest_10                  string
SQL>!


Ø  Take a fresh full database backup using RMAN