Updates from February, 2011 Toggle Comment Threads | Keyboard Shortcuts

• 

  levin 11:08 pm on February 19, 2011 Permalink | Reply
  Tags: oracle ( 17 ), rman   

  Getting ORA-01180 error during database restoration 

  Why

  Having restore database from rman but got a ORA-01180 error, it happen often because your backup file source directory is different then original backup path.

  Let’s try

  Restore pfile and recreate all necessary directory eg: archive log, admin, a/b/c/udump, datafile… every directory you specified in the pfile

  Copy your backup pfile to %ORACLE_HOME%\dbs\

  sqlplus / as sysdba
  sqlplus> startup nomount pfile='%ORACLE_HOME%\dbs\initORAINST.ora'
  

  Restore controlfile

  rman target /
  RMAN> restore controlfile from 'D:\path\to\controlfile.bak'
  

  Restore database

  RMAN> alter database mount;
  RMAN> restore database;
  

  Then you got a similar message as below, because originally your backup path is somewhere different then your current backup file source path.

  creating datafile fno=1 name=D:\ORADATA\ORAINST\SYSTEM01.DBF
  RMAN-00571: ===========================================================
  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  RMAN-00571: ===========================================================
  RMAN-03002: failure of restore command at 02/18/2011 16:55:06
  ORA-01180: can not create datafile 1
  ORA-01110: data file 1: 'D:\ORADATA\ORAINST\SYSTEM01.DBF'
  

  Try this to re-catalog your backup files then re-run the restore

  rman> catalog start with 'D:\backup\source'

   

  Reply Cancel reply

  Required fields are marked *

  Name *

  Email *

  Website

  

  

  

  
  

  CAPTCHA Code *

  

• 

  levin 5:05 pm on January 14, 2011 Permalink | Reply
  Tags: mysql ( 3 )   

  MySQL tuning for dummies 

  Introduction

  There is so many people asking how to improve their mysql instance, and also so many replies that increase key_buffer, sort_buffer, cache size… etc… which believe will lighten up the performance. However, fellows never notice those parameters was actually efficient to their setup.

  So here you are MySqlTuner,
  (More …)

  levin, Florin, and Singularity are discussing. Toggle Comments

   
  • 

    Singularity 11:30 pm on January 21, 2011 Permalink | Reply

    how do you figure this is for “dummies”?!

    • 

      levin 11:34 pm on January 21, 2011 Permalink | Reply

      Because I’m the dummy one.. :P

  • 

    Florin 3:20 am on January 25, 2011 Permalink | Reply

    “./mysqltuner.pl” didn’t worked for me .
    I made it work with “pearl mysqltuner.pl”

    • 

      levin 3:29 am on January 25, 2011 Permalink | Reply

      try “chmod +x mysqltuner.pl” first, then you can run it without specifying “perl”

• 

  levin 2:48 am on June 12, 2010 Permalink | Reply
  Tags: oracle ( 17 )   

  Connect with local sysdba failed when database start up by CRS 

  What happen?

  A database started up by CRS successfully, but you can’t connect it by local sysdba “sqlplus / as sysdba”, however tnsping or connect with connection string “sqlplus system@prddb” works fine.
  Later, you tried to start the database alone manually, it works fine with both connection method.

  You inspected every crs log and alertlog but it look seems fine.

  Oops, why it inconsistent?

  When clusterware trying to startup your oracle, it’ll use $ORACLE_HOME/bin/racgwrap , so make sure your system environment setting is same as the ORACLE_HOME setting in racgwrap script.

   
• 

  levin 5:52 pm on April 27, 2010 Permalink | Reply
  Tags: mysql ( 3 )   

  MySQL multiple instance on CentOS howto 

  Create new database instance on new destination

  mkdir /u02/mysql
  mkdir /u02/mysql/data
  mkdir /u02/mysql/log
  mkdir /u02/mysql/run
  mkdir /u02/mysql/lock
  
  chown -R mysql:mysql /u02/mysql
  mysql_install_db --datadir=/u02/mysql/data --user=mysql
  

  (More …)

  Joris Conijn, levin, Murad and one other person are discussing. Toggle Comments

   
  • 

    Murad 1:11 pm on August 19, 2010 Permalink | Reply

    hello levin…
    after doing ‘ service mysqld.server2 start ‘ i’m getting one error.
    the error is
    ‘ mysqld.server2: unrecognized service ‘
    hope you can help me …
    thanks in advance

    –
    murad

  • 

    levin 2:13 pm on August 19, 2010 Permalink | Reply

    Hi Murad,

    How about /etc/rc.d/init.d/mysqld.server2 start ?

  • 

    Joris Conijn 5:50 pm on October 21, 2010 Permalink | Reply

    Murad, try the following:

    $ chmod +x /etc/init.d/mysqld.server2
    $ service mysqld.server2 start

    The start script in init.d should be executable

• 

  levin 5:59 pm on April 13, 2010 Permalink | Reply
  Tags: oracle ( 17 )   

  Oracle PRKN-1008 Error 

  PRKH-1010 : Unable to communicate with CRS services.
  PRKH-1000 : Unable to load the SRVM HAS shared library
  PRKN-1008 : Unable to load the shared library "srvmhas10"
  
  ..... Can't find library srvmhas (libsrvmhas10.a or .so) or any library file
  

  Possibility

  • File is missing in LIBPATH
  • Wrong LD_LIBRARY_PATH being set.
  • The .so , . a file is unreadable by oracle user in CRS_HOME/lib | lib32 or ORACLE_HOME/lib | lib32
  • “ar” command was broken
   
• 

  levin 2:30 pm on December 31, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts ( 22 )   

  How to determine undo usage in Oracle 

  Overview

  Undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued. Automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

  This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

  Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

  With the below information gathered by SQL query, you may have a idea to prevent undo space is running out so quickly.
  (More …)

  Raj Gopal, and Chandrasekar Sivasamy are discussing. Toggle Comments

   
  • 

    Chandrasekar Sivasamy 9:12 pm on July 3, 2010 Permalink | Reply

    Thank you so much. It’s very useful. I have cleared my doubt.

  • 

    Raj Gopal 10:12 pm on January 20, 2011 Permalink | Reply

    The query to calculate the required undo tablespace size is very helpful.

• 

  levin 2:10 pm on December 31, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts ( 22 )   

  How to check tablespace usage in Oracle 

  Check each schema usage on tablespaces.

  set linesize 300
  set pagesize 20
  set feedback off
  
  select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"
  from dba_segments a, (select tablespace_name, sum(bytes) total_space
                        from dba_data_files
                        group by tablespace_name) b
  where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')
  and a.tablespace_name = b.tablespace_name
  group by a.tablespace_name, a.owner, b.total_space/1024/1024
  order by a.tablespace_name, a.owner;
  

  Check overall tablespaces usage.

  set linesize 300
  set pagesize 20
  set feedback off
  
  select t1.tsp_name, sysdate,
         round(t1.total_space/1024/1024,2) "Total (MB)",
         round((t1.total_space-t2.free_bytes)/1024/1024,2) "Used (MB)",
         round(t2.free_bytes/1024/1024,2) "Free (MB)",
         round(t2.free_bytes/t1.total_space*100,2) "% Free",
         decode(greatest(round(t2.free_bytes/t1.total_space*100,2),20),20,'*',' ') D,
         decode(greatest(round(t2.free_bytes/t1.total_space*100,2),15),15,'*',' ') E
  from (select tablespace_name tsp_name, sum(bytes) total_space
        from sys.dba_data_files
        where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
        group by tablespace_name) t1,
       (select tablespace_name tsp_name, sum(bytes) free_bytes
        from sys.dba_free_space
        where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
        group by tablespace_name) t2
  where t1.tsp_name = t2.tsp_name
  order by tsp_name;
  

   
• 

  levin 11:21 am on December 31, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts ( 22 )   

  How to find active SQL statements in Oracle 

  Everyone has a favorite script to find the active SQL statements. Here is one of mine, it’s RAC friendly too..

  
       set serverout on size 999999
       declare
       begin
       dbms_output.put_line(' ');
       dbms_output.put_line('************* Start report for WAITING sessions with current SQL ***************');
       for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
       vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
       from gv$session_wait vsw, gv$session vs
       where vsw.sid = vs.sid
       and vsw.inst_id = vs.inst_id
       and vs.type <> 'BACKGROUND'
       and vsw.event NOT IN ('rdbms ipc message'
       ,'smon timer'
       ,'pmon timer'
       ,'SQL*Net message from client'
       ,'lock manager wait for remote message'
       ,'ges remote message'
       ,'gcs remote message'
       ,'gcs for action'
       ,'client message'
       ,'pipe get'
       ,'Null event'
       ,'PX Idle Wait'
       ,'single-task message'
       ,'PX Deq: Execution Msg'
       ,'KXFQ: kxfqdeq - normal deqeue'
       ,'listen endpoint status'
       ,'slave wait'
       ,'wakeup time manager'))
       loop
       begin
       dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
       dbms_output.put_line('************************* ******************** ****** *********** ********** *************');
       dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||'
       '|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
       dbms_output.put_line(' SQLText ');
       dbms_output.put_line('****************************************************************');
       for y in (select sql_text
       from gv$sqltext
       where address = x.sql_address
       and hash_value = x.sql_hash_value
       and inst_id = x.inst_id
       order by piece)
       loop
       dbms_output.put_line(y.sql_text);
       end loop;
       end;
       end loop;
       dbms_output.put_line('************** End re! port for sessions waiting with current SQL ****************');
       dbms_output.put_line(' ');
       end;
       /
  

  http://www.oracle.com/technology/oramag/code/tips2004/062104.html