Monday, July 26, 2010

Blocking session ... show table records that are blocked (Part II)

In one of mine previous posts that deal with blocking sessions (Blocking session ... show table records that are blocked), I have shown how to identify row which is blocked.

Recent ask from mine friend gave me an idea how to expand information with waiting SQL statement itself (show real sql that is in waiter session waiting to be run).

Regardless the solution is very easy (just add next code in the end of existing sbr.sql script):
SELECT t.sql_text
  INTO  l_sql
  FROM gv$sqlarea t, 
       gv$session s 
 WHERE t.address = s.sql_address AND 
       t.hash_value = s.sql_hash_value AND 
       s.inst_id=rec_waiters.inst_id AND
       s.SID = rec_waiters.sid AND 
       s.sql_hash_value != 0
;
dbms_output.put_line(lpad(chr(9),9)|| chr(9)|| 
                     '('||
                     rpad(l_sql,100,' ')||
                     ')'
                     );
I'll show the whole solution. sbr.sql script in complete code that include current improvement:
CREATE OR REPLACE procedure TOOLS.show_blocked_records (
/* $Header: show_blocked_records.prc 1.1 09/20/2010 20:45 damirv $ */
/* ---------------------------------------------------------------------------

           Copyright(C) 1995-2010 TEB Informatika d.o.o.


 Filename: sbr.sql (show_blocked_records)
 CR/TR#  : 
 Purpose : Find blocking records by locked session for RAC and non RAC environment  
           Resulting SQL shows rows which are locked and their release will continue 
           running blocking session.
           
           Excellent way to find hot records (hot tables)
           
           
 Date    : 19.05.2010.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g/11g (should work on lower versions as well)
           
           "c_nr_cols" define number of columns (without pk columns!) of generated sql 
                       change it to your needs           

 Changes (DD.MM.YYYY, Name, CR/TR#):
          12.07.2010 Damir Vadas
                     added support for current waiter sql statement and 
          19.08.2010 Damir Vadas
                     added exception block for no_data_found case
                     added "substr" for l_sql parse
          01.09.2010 Damir Vadas
                     fixed bug blockers and waiters to be 11g compatible
          03.09.2010 Damir Vadas
                     added "Locked object is not table" for "starnge" locks in 11g
          20.09.2010 Damir Vadas
                     Reunited logic for script/procedure (change value for b_is_script)
--------------------------------------------------------------------------- */ 
                                   s_mail_text OUT VARCHAR2 )
AS
  b_is_script CONSTANT BOOLEAN DEFAULT TRUE;
  const_nr_cols CONSTANT PLS_INTEGER := 3;
  --s_mail_text  VARCHAR2(4000);    
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
  
  -- blockers ....
  CURSOR c_blockers IS
    SELECT DISTINCT 
           NVL(username,'BLOCKER ???') blocker_user, 
           gvb.sid, 
           gvs.serial# serial, 
           gvb.inst_id, 
           gvb.ctime ,
           gvs.status STATUS,      
           module, 
           action,
           decode(gvb.type, 
                     'AE', 'Edition Lock',
                     'BL','Buffer hash table',
                     'CF','Control File Transaction',
                     'CI','Cross Instance Call',
                     'CS','Control File Schema',
                     'CU','Bind Enqueue',
                     'DF','Data File',
                     'DL','Direct-loader index-creation',
                     'DM','Mount/startup db primary/secondary instance',
                     'DR','Distributed Recovery Process',
                     'DX','Distributed Transaction Entry',
                     'FI','SGA Open-File Information',
                     'FS','File Set',
                     'IN','Instance Number',
                     'IR','Instance Recovery Serialization',
                     'IS','Instance State',
                     'IV','Library Cache InValidation',
                     'JQ','Job Queue',
                     'KK','Redo Log "Kick"',
                     'LS','Log Start/Log Switch',
                     'MB','Master Buffer hash table',
                     'MM','Mount Definition',
                     'MR','Media Recovery',
                     'PF','Password File',
                     'PI','Parallel Slaves',
                     'PR','Process Startup',
                     'PS','Parallel Slaves Synchronization',
                     'RE','USE_ROW_ENQUEUE Enforcement',
                     'RT','Redo Thread',
                     'RW','Row Wait',
                     'SC','System Commit Number',
                     'SH','System Commit Number HWM',
                     'SM','SMON',
                     'SQ','Sequence Number',
                     'SR','Synchronized Replication',
                     'SS','Sort Segment',
                     'ST','Space Transaction',
                     'SV','Sequence Number Value',
                     'TA','Transaction Recovery',
                     'TD','DDL enqueue',
                     'TE','Extend-segment enqueue',
                     'TM','DML enqueue',
                     'TO', 'Temp Object', 
                     'TS','Temporary Segment',
                     'TT','Temporary Table',
                     'TX','Transaction',
                     'UL','User-defined Lock',
                     'UN','User Name',
                     'US','Undo Segment Serialization',
                     'WL','Being-written redo log instance',
                     'WS','Write-atomic-log-switch global enqueue',
                     'XA','Instance Attribute',
                     'XI','Instance Registration', 
                     decode(substr(gvb.type,1,1),
                                 'L','Library Cache     ('||substr(gvb.type,2,1)||')', -- Library cache lock instance lock (A..P = namespace)
                                 'N','Library Cache Pin ('||substr(gvb.type,2,1)||')', -- Library cache pin instance (A..Z = namespace)
                                 'Q','Row Cache         ('||substr(gvb.type,2,1)||')', -- Row cache instance (A..Z = cache)
                                 '????'
                     ),
                     gvb.type 
           ) lock_type
     FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvb.sid=gvs.sid
      AND gvb.inst_id=gvs.inst_id
  ORDER BY CTIME desc
  ;
  
  --waiters   
  CURSOR c_waiters (cp_blocker_waiter_sid gv$lock.sid%TYPE, cp_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT 
           NVL(username,'WAITER ???') waiter_user, 
           gvs.sid, 
           gvs.serial# serial, 
           gvs.inst_id, 
           gvw.ctime , 
           gvs.status STATUS,
           module,
           action,
           decode(gvw.request, 
                              0, 'None',
                              1, 'NoLock',
                              2, 'Row-Share',
                              3, 'Row-Exclusive',
                              4, 'Share-Table',
                              5, 'Share-Row-Exclusive',
                              6, 'Exclusive',
                                 'Nothing-'
           ) lock_req
     FROM gv$lock gvw, gv$session gvs
    WHERE 1=1
      AND gvw.request>0
      AND gvw.lmode=0
      AND gvw.sid=gvs.sid
      AND gvw.inst_id=gvs.inst_id
      AND gvs.blocking_session=cp_blocker_waiter_sid 
      AND gvs.blocking_instance=cp_blockers_waiter_inst_id
  ORDER BY CTIME desc
  ;

  -- blocked records (this is allways one object with one locked row)
  CURSOR c_blocked_objects (cp_sid IN PLS_INTEGER, cp_inst_id IN PLS_INTEGER) IS  
    SELECT do.owner,
           do.object_name,
           do.object_type,
           dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) RID
    FROM gv$session s
        ,dba_objects do
    WHERE inst_id=cp_inst_id 
      AND sid=cp_sid
      AND s.ROW_WAIT_OBJ# = do.OBJECT_ID
  ;
  
  -- columns which user want to see (pk keys are excluded because they are shown by default!)
  CURSOR c_cols (cp_owner IN VARCHAR2, cp_table IN VARCHAR2, cc_nr_cols IN PLS_INTEGER) IS  
    WITH q as (
                SELECT column_name
                  FROM all_tab_columns
                 WHERE owner= cp_owner 
                   AND table_name = cp_table     
                   AND column_name NOT IN ( SELECT b.column_name column_name_pk
                                              FROM all_constraints a, 
                                                   all_cons_columns b
                                             WHERE a.OWNER = cp_owner
                                               AND a.table_name = cp_table
                                               AND a.constraint_type='P'
                                               AND a.constraint_name=b.constraint_name
                                                 
                                           )     
                ORDER BY column_id
              )
    SELECT q.column_name
      FROM q
     WHERE rownum<=cc_nr_cols
  ;
  
  -- pk_key columns (always shown in the front)
  CURSOR c_pkeys (cp_owner IN VARCHAR, cp_table IN VARCHAR) IS 
      SELECT b.column_name column_name_pk
        FROM all_constraints a, 
             all_cons_columns b
       WHERE a.owner = cp_owner
         AND a.table_name = cp_table
         AND a.constraint_type='P'
         AND a.constraint_name=b.constraint_name
      ORDER BY position
  ;  
  
  stmt  VARCHAR2 (4000);

  -- for showing current waiter sql
  l_sql VARCHAR2 (4000); 
  
  l_can_obtain_locked_row boolean;
  l_temp VARCHAR2(64); 
 
  -- for finding locked record in non DML blocks
  l_rowid ROWID; 
  
  PROCEDURE add_message_line (p_text VARCHAR2) 
  IS
  BEGIN
    IF b_is_script THEN
      dbms_output.put_line (p_text);
    ELSE
      s_mail_text := s_mail_text || p_text || chr(10);
    END IF;
  END;
  
  FUNCTION create_SELECT_stmt (p_owner       VARCHAR2, 
                               p_object_name VARCHAR2,
                               p_row_id      VARCHAR2,
                               p_nr_cols     NUMBER DEFAULT const_nr_cols
                              ) RETURN       VARCHAR2
  IS
  BEGIN
    stmt := 'SELECT ';
    -- place pks cols in the front of select
    FOR rec_pkeys IN c_pkeys (p_owner, p_object_name) LOOP
      stmt := stmt || rec_pkeys.column_name_pk ||' , '; 
    END LOOP;  
    -- then show other "c_nr_cols" number of cols (pks are not counted)
    FOR rec_cols IN c_cols (p_owner, p_object_name, const_nr_cols) LOOP
      stmt := stmt ||  rec_cols.column_name || ' , ' ;
    END LOOP;
    -- remove last added " , "
    IF SUBSTR (stmt,LENGTH(stmt)-2)=' , ' THEN
      stmt  :=  SUBSTR (stmt,1, (LENGTH(stmt)-3));
    END IF;
    stmt := stmt || ' FROM ' || p_owner ||'.'|| p_object_name ;
    -- get involved rowid for this session (there is always one blocking row!)        
    stmt := stmt || ' WHERE rowid = ' || ''''||  p_row_id || '''' || ' ;';
    return stmt;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN null;
  END; 
BEGIN 
  IF b_is_script THEN
    dbms_output.put_line (chr(9));
    dbms_utility.db_version(db_ver,db_ver2);
    dbms_output.put_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  END IF;
  dbms_output.put_line (chr(9));
  FOR rec_blockers IN c_blockers LOOP
    add_message_line(rec_blockers.blocker_user||' ('||rec_blockers.inst_id|| ' '''||rec_blockers.sid||','||rec_blockers.serial||''')');
    FOR rec_waiters IN c_waiters (rec_blockers.sid,rec_blockers.inst_id) LOOP 
      IF b_is_script THEN
        add_message_line (chr(255)|| 
                          rpad(rec_waiters.waiter_user||' (' || rec_waiters.inst_id||' '||rec_waiters.sid||','||rec_waiters.serial||')',20,' ')||
                          lpad(to_char(rec_waiters.ctime),6,' ')||' sec   '|| 
                          lpad(to_char(rec_waiters.lock_req),13,' ')||' '|| 
                          lpad(rec_waiters.status,15,' ')|| ' '|| 
                          lpad(to_char(nvl(rec_waiters.module,'?')),15,' ')
                          );
      ELSE
        add_message_line (chr(9)|| chr(9)||
                          rpad(rec_waiters.waiter_user||' (' || rec_waiters.inst_id||' '||rec_waiters.sid||','||rec_waiters.serial||')',20,' ')||
                          lpad(to_char(rec_waiters.ctime),6,' ')||' sec   '|| 
                          lpad(to_char(rec_waiters.lock_req),13,' ')||' '|| 
                          lpad(rec_waiters.status,15,' ')|| ' '|| 
                          lpad(to_char(nvl(rec_waiters.module,'?')),15,' ')
                          );                          
      END IF;                          
      stmt := null;
      FOR rec_blocked_objects IN c_blocked_objects (rec_waiters.sid,rec_waiters.inst_id) LOOP
        stmt := 'SELECT ';
       
        -- place pks cols in the front of select
        FOR rec_pkeys IN c_pkeys (rec_blocked_objects.owner, rec_blocked_objects.object_name) LOOP
          stmt := stmt || rec_pkeys.column_name_pk ||' , '; 
        END LOOP;  
        
        -- then show other "c_nr_cols" number of cols (pks are not counted)
        FOR rec_cols IN c_cols (rec_blocked_objects.owner, rec_blocked_objects.object_name, const_nr_cols) LOOP
          stmt := stmt ||  rec_cols.column_name || ' , ' ;
        END LOOP;
        -- remove last added " , "
        IF SUBSTR (stmt,LENGTH(stmt)-2)=' , ' THEN
          stmt  :=  SUBSTR (stmt,1, (LENGTH(stmt)-3));
        END IF;
        stmt := stmt || ' FROM ' || rec_blocked_objects.owner ||'.'|| rec_blocked_objects.object_name ;
        
        -- get involved rowid for this session (there is always one blocking row!)        
        stmt := stmt || ' WHERE rowid = ' || ''''||  rec_blocked_objects.RID || '''' || ' ;';
        add_message_line(chr(9)|| stmt);
        -- current sql statement support
        BEGIN
          SELECT substr(t.sql_text,1,3999)
          INTO l_sql
          FROM gv$sqlarea t, 
               gv$session s 
          WHERE t.address = s.sql_address AND 
               t.hash_value = s.sql_hash_value AND 
               s.inst_id=rec_waiters.inst_id AND
               s.SID = rec_waiters.sid AND 
               s.sql_hash_value != 0
          ;
          IF b_is_script THEN
            add_message_line(chr(9)||
                                 '('||
                                 l_sql||
                                 ')'
                                 );
          ELSE
            add_message_line(chr(9)|| chr(9)||
                                 '('||
                                 l_sql||
                                 ')'
                                 );
          END IF;
        EXCEPTION
          WHEN no_data_found THEN
            add_message_line('Cannot retrieve SQL in this moment. Please try again!');
        END;
      END LOOP; 
      IF stmt is null THEN
        FOR rec_blocked_objects IN c_blocked_objects (rec_blockers.sid,rec_blockers.inst_id) LOOP
          add_message_line(chr(9)||chr(9)|| 'Locked object is not table: '||rec_blocked_objects.owner||'.'||rec_blocked_objects.object_name||' ('||rec_blocked_objects.object_type||')');
        END LOOP;
      END IF;      
    END LOOP;
  END LOOP;
  s_mail_text  := RTRIM (s_mail_text, chr(9));
  -- chr(9) is the only content
  IF length(s_mail_text) IN (0,1) THEN
    s_mail_text  := null;
  END IF; 
END;
/

Quick example

Suppose we have a table xx with one column (C1) and two rows in it as:
SQL>select * from xx;

C1
----------
2
3

SQL>
Here is SQL from blocker's session:
SQL>update xx set c1='1' where c1='2';

1 row updated.

SQL>
And in another (waiter session) there is SQL:
SQL>update xx set c1='123';

This command "hang" ... waiting for lock to be released! In third (monitoring session) we can call previously shown sbr.sql script:
SQL>@sbr
DAMIRV (1 '439,2117')
        SYS       1     499,25445        40 sec       Exclusive         ACTIVE        SQL*Plus
                SELECT C1 FROM DAMIRV.XX WHERE rowid = 'AABIYyAAKAAALONAAA' ;
                (update damirv.xx set c1='123'                                    )

PL/SQL procedure successfully completed.

SQL>
Interpetation is very obvious. SYS session (inst_id=1, 499,25445) is blocked by DAMIRV session (inst_id=1, 439,2117). SYS session wait for the release of record:
SELECT C1 FROM XX WHERE rowid = 'AABIYyAAKAAALONAAA' ;
And SYS has executed SQL (which is hanged):
update damirv.xx set c1='123'
What shows all important data about any kind of lock!

Be proactive

If you incorporate mentioned sbr.sql port of code into sb.sql script in a way that create show_blocked_records function that return blocking information (rather then use dbms_output.put_line kind of output), then all you have to do is insert following lines inside existing code:
-- previous code from sb.sql
  ...
  IF bHasAny THEN -- this line already exist
    add_message_line (chr(9));
    show_blocked_records (s_blocked_rows);
    add_message_line (s_blocked_rows);
  ...
  -- following code from sb.sql
you'll get complete blocking monitoring system with full explanation of the problem.

The End

I have rearranged sbr.sql script to understand locking that was shown on Deadlock on Oracle 11g but Not on 10g. This is why I put part of code that shows locked object in a case that it is not table. Here is output of mine function for his example. Here oner of all objects is DAMIRV:
Oracle version: 11.2.0.1.0 (11.1.0.0.0)
        
DAMIRV (1 '133,10')
        DAMIRV    1     18,9           1596 sec   Share-Row-Exc         ACTIVE        SQL*Plus
                Locked object is not table: DAMIRV.SYS_C0022027 (INDEX)

PL/SQL procedure successfully completed.

SQL> 
As you see sometimes fk index can be a power full blocker.

Cheers!

Monday, July 19, 2010

Cloning a node/cluster in Oracle RAC

In mine previous post (Clone 11g ORACLE_HOME (different box, different path) on Linux) I have described how is easy (and wise) to clone ORACLE_HOME on described way.

Once a RAC cluster with ASM is successfully installed, the ASM and RAC setup can now be cloned to new cluster (new node). While it was possible before 11g to clone RAC and ASM to a new node in the same cluster, there is now the capability to clone entire clusters for easy deployment. This is a scripted method of quickly deploying RAC across the grid. ln order to clone ASM and RAC configurations, the mentioned script clone.pl is used again along with pre-scripted silent runs of NetCA and DBCA. As said before, the perl scriptclone.pl can be used to clone all ORACLE_HOME's
  • ASM ORACLE_HOME
  • Any Database ORACLE_HOME
as well. Do not forget that Clusterware ORACLE_HOME is currently not capable of cloning in a mentioned way-it has to be installed (and patched) manually in an old fashioned way.

So a useful extension on previous topic may be:
  1. Cloning a single node (adding new)
  2. Cloning a whole RAC to another RAC

So I'd like to express something that might help and it's been proven in praxis on 11gR2 database.

The Solution

Regardless adding new node is very well described in Adding New Nodes to Your Oracle RAC 10g Cluster on Linux, if you deal with several ORACLE_HOMES (ASM and at least another one for database...and maybe more) which are heavily patched, following from "Step 5: Install Oracle Software" to end may a really curious task!

Here are the steps involved in cloning RAC instances with ASM to another cluster (new node):
Install Oracle Clusterware on the new node(s)
  1. With procedure express in prevous topic create tar backup of the Oracle 11g software
  2. Backup the ASM HOME
  3. Backup the DB HOME (or more of them if exists!)
  4. Restore the ASM tar backup onto the new cluster nodes
  5. Run the clone.pl Perl script on each node for ASM
  6. Run the root.sh script on each node as root for ASM
  7. Run NetCA in silent mode (using $ORACLE_HOME/network/install/netca_typ.rsp file) to create listeners on each node
  8. Run DBCA in silent mode to create the ASM instance on each node
  9. Restore the DB tar backup onto the new clusters nodes
  10. Run the clone.pl Perl script on each node for DB Home
  11. Run thc root.sh script as root on each node for DB Home
  12. Run DBCA to create the RAC instances
As you see the very same procedure may be use when we want to add another node to existing RAC configuration. Detailed steps for that action may be found in Oracle document Using Cloning to Add ASM and Oracle RAC to Nodes in a New Cluster.

Cheers!

Friday, July 9, 2010

Clone 11g ORACLE_HOME (different box, different path) on Linux


The problem

When you need to create testing Oracle database environment, the best option is to have exactly same ORACLE_HOME that is in production. Unfortunately many running productions are heavily patched and if you do not have really proper documentation (many of us has inherited work from someone before), it is very hard to create exact clone of ORACLE_HOME by manual approach-installation from the beginning.

From eBS 11.5.10.2, when "rapid clone" was properly implemented, Oracle APPS DBA's has a nice tool (in $ORACLE_HOME/clone directory) to do that regardless underlying ORACLE_HOME was 10g version. For classic 10g database installations, this was not possible.

In Oracle 11g, this functionality was ported to standalone database installations (non EBS) and DBA's can use it with great pleasure. And this saves DBA's life a lot.

The solution

The whole task is divided on source part (where original ORACLE_HOME is placed) and target part (where new, cloned ORACLE_HOME should be established).

Environments

I'll explain how to clone ORACLE_HOME to different box (different server), different path...what covers any cloning situation that you may fall into!

Source

Source define environment that will be used as source for cloning. In mine case this is vmcentosprod server. Mine "best practice" for Oracle on Linux is to have one ".env" file for each database in each ORACLE_HOME. In this case this is EUCHARIS.env file whose content is:
#!/bin/sh

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=vmcentos; export ORACLE_HOSTNAME
ORACLE_BASE=/u01; export ORACLE_BASE

ORACLE_SID=EUCHARIS; export ORACLE_SID
export PS1="[\u $ORACLE_SID@\h \W]\$ "

ORACLE_HOME=$ORACLE_BASE/EUCHARIS/eucharisdb/11.2; export ORACLE_HOME

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=${PATH}:$HOME/bin:$ORACLE_HOME/bin; export PATH
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/sbin; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/bin export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib; export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

alias q="rlwrap $ORACLE_HOME/bin/sqlplus"

export SQLPATH="/home/oracle/admin_scripts"

DB_NAME=EUCHARIS; export DB_NAME
So most important values for cloning are:
ORACLE_BASE=/u01
ORACLE_HOME=/u01/EUCHARIS/eucharisdb/11.2
ORACLE_HOSTNAME=vmcentosprod
ORACLE_SID=EUCHARIS

Target

Target server is vmcentos. New EUCARIS.env was done by sarch/replace of original EUCHARIS.env file and changing just one value for ORACLE_BASE from "u01" to "/u02". Here is the whole content of new EUCARIS.env file:
#!/bin/sh

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=vmcentos; export ORACLE_HOSTNAME
ORACLE_BASE=/u02; export ORACLE_BASE

ORACLE_SID=EUCARIS; export ORACLE_SID
export PS1="[\u $ORACLE_SID@\h \W]\$ "

ORACLE_HOME=$ORACLE_BASE/EUCARIS/eucarisdb/11.2; export ORACLE_HOME

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=${PATH}:$HOME/bin:$ORACLE_HOME/bin; export PATH
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/sbin; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/bin export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib; export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

alias q="rlwrap $ORACLE_HOME/bin/sqlplus"

export SQLPATH="/home/oracle/admin_scripts"

DB_NAME=EUCARIS; export DB_NAME
So most important values for target are:
ORACLE_BASE=/u02
ORACLE_HOME=/u02/EUCARIS/eucarisdb/11.2
ORACLE_HOSTNAME=vmcentos
ORACLE_SID=EUCARIS
As you see we have totally different environments: different box and different path (notice that "h" is missing in target string EUCHARIS->EUCARIS, eucharisdb->eucarisdb). Not to mention that duplicated database will also have different name but this is over the scope of this article.
Just to mention that on both servers, owner of "/u01" and "/u02" directories is oracle user, which is in dba group. DBA group is used for installation as well for active work. If this is not your case then adopt it.

1. Tarring source ORACLE_HOME

First step is to create tar of whole ORACLE_HOME on source server
[oracle@vmcentosprod u01]$ cd /u01/EUCHARIS/eucharisdb/
[oracle@vmcentosprod eucharisdb]$ tar cvf eucharis.tar /u01/EUCHARIS/eucharisdb/11.2
11.2/
11.2/crs/
11.2/crs/sbs/
11.2/crs/sbs/clsrwrap.sbs
11.2/crs/sbs/localconfig.sbs
...
u01/EUCHARIS/eucharisdb/11.2/sqlplus/admin/pupbld.sql
u01/EUCHARIS/eucharisdb/11.2/sqlplus/admin/plustrce.sql
u01/EUCHARIS/eucharisdb/11.2/dc_ocm/
u01/EUCHARIS/eucharisdb/11.2/dc_ocm/clean_cron_proc.sh
[oracle@vmcentosprod eucharisdb]$

2. Copy eucharis.tar file to target server

[oracle@vmcentosprod eucharisdb]$ scp -rp eucharis.tar oracle@vmcentos:/u02
If you are creating ORACLE_HOME on the same server then this step may be skipped!

3. Unpack eucharis.tar file on target server

On target server in /u02 directory create EUCARIS directory and position in it.
[oracle@vmcentos eucharisdb]$ mkdir /u02/EUCARIS
[oracle@vmcentos eucharisdb]$ cd /u02/EUCARIS/
Untar, previously copied eucharis.tar file:
[oracle@vmcentos EUCARIS]$ tar xvf /u02/eucharis.tar
u01/EUCHARIS/eucharisdb/11.2/
u01/EUCHARIS/eucharisdb/11.2/crs/
u01/EUCHARIS/eucharisdb/11.2/crs/sbs/
...
u01/EUCHARIS/eucharisdb/11.2/sqlplus/admin/plustrce.sql
u01/EUCHARIS/eucharisdb/11.2/dc_ocm/
u01/EUCHARIS/eucharisdb/11.2/dc_ocm/clean_cron_proc.sh
[oracle@vmcentos EUCARIS]$ 
As you see mine practice is to keep full path from source...what saves me sometimes in careless unpacking.
Last is to move unpacked "11.2" directory with it's content to proper place.
[oracle@vmcentos EUCARIS]$ mkdir /u02/EUCARIS
[oracle@vmcentos EUCARIS]$ mkdir /u02/EUCARIS/eucarisdb
[oracle@vmcentos EUCARIS]$ mv /u02/EUCARIS/u01/EUCHARIS/eucharisdb/11.2/ /u02/EUCARIS/eucarisdb/
Checking that all is consistent on target side (user, date, owner):
[oracle@vmcentos EUCARIS]$ cd /u02/EUCARIS/eucarisdb/11.2
[oracle@vmcentos 11.2]$ ll
total 316
drwxr-xr-x  3 oracle dba  4096 Apr  7 11:00 admin
drwxr-xr-x  8 oracle dba  4096 Jul  1 10:02 apex
drwxr-xr-x  8 oracle dba  4096 Apr  7 10:50 assistants
drwxr-xr-x  2 oracle dba 12288 Apr  7 11:17 bin
drwxr-xr-x  7 oracle dba  4096 Apr  7 11:03 ccr
drwxr-xr-x  3 oracle dba  4096 Apr  7 11:04 cdata
drwxr-xr-x  4 oracle dba  4096 Apr  7 11:17 cfgtoollogs
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:58 clone
drwxr-xr-x  2 oracle dba  4096 Apr  7 11:04 config
drwxr-xr-x  6 oracle dba  4096 Apr  7 10:50 crs
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 csmig
drwxr-xr-x  6 oracle dba  4096 Apr  7 11:04 css
drwxr-xr-x 10 oracle dba  4096 Apr  7 11:05 ctx
drwxr-xr-x  6 oracle dba  4096 Apr  7 10:50 cv
drwxr-xr-x  4 oracle dba  4096 Jul  9 07:29 dbs
drwxr-xr-x  2 oracle dba  4096 Apr  7 11:01 dc_ocm
drwxr-xr-x  4 oracle dba  4096 Apr  7 11:08 deinstall
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 demo
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 diagnostics
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:50 dv
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 emcli
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 has
drwxr-xr-x  5 oracle dba  4096 Apr  7 11:08 hs
drwxr-xr-x  8 oracle dba  4096 Apr  7 10:50 ide
drwxr-xr-x  8 oracle dba  4096 Apr  7 11:17 install
-rw-r--r--  1 oracle dba    37 Apr  7 10:50 install.platform
drwxr-xr-x  2 oracle dba  4096 Apr  7 10:55 instantclient
drwxr-x--- 13 oracle dba  4096 Apr  7 11:16 inventory
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:59 j2ee
drwxr-xr-x  8 oracle dba  4096 Apr  7 10:50 javavm
drwxr-xr-x  3 oracle dba  4096 Apr  7 11:02 jdbc
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:50 jdev
drwxr-xr-x  6 oracle dba  4096 Apr  7 11:02 jdk
drwxr-xr-x  2 oracle dba  4096 Apr  7 11:01 jlib
drwxr-xr-x 12 oracle dba  4096 Apr  7 11:04 ldap
drwxr-xr-x  4 oracle dba 12288 Apr  7 11:08 lib
drwxr-xr-x  4 oracle dba  4096 Apr  7 12:34 log
drwxr-xr-x  6 oracle dba  4096 Apr  7 10:50 md
drwxr-xr-x  2 oracle dba  4096 Apr  7 10:54 mesg
drwxr-xr-x  6 oracle dba  4096 Apr  7 11:04 mgw
drwxr-xr-x 11 oracle dba  4096 Apr  7 11:08 network
drwxr-xr-x  6 oracle dba  4096 Apr  7 10:50 nls
drwxr-xr-x 20 oracle dba  4096 Apr  7 10:55 oc4j
-rw-------  1 oracle dba  1772 Apr  7 11:04 ocm.rsp
drwxr-xr-x  6 oracle dba  4096 Apr  7 10:50 odbc
drwxr-xr-x  5 oracle dba  4096 Apr  7 10:50 olap
drwxr-xr-x  5 oracle dba  4096 Apr  7 10:50 ons
drwxr-xr-x  6 oracle dba  4096 Apr  7 11:03 OPatch
drwxr-xr-x  7 oracle dba  4096 Apr  7 11:04 opmn
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:50 oracore
-rw-r-----  1 oracle dba    43 Apr  7 10:50 oraInst.loc
drwxr-xr-x  8 oracle dba  4096 Apr  7 10:50 ord
drwxr-xr-x  7 oracle dba  4096 Apr  7 11:03 oui
drwxr-xr-x 26 oracle dba  4096 Apr  7 11:04 owb
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:50 owm
drwxr-xr-x  5 oracle dba  4096 Apr  7 11:08 perl
drwxr-xr-x  6 oracle dba  4096 Apr  7 10:50 plsql
drwxr-xr-x  7 oracle dba  4096 Apr  7 10:50 precomp
drwxr-xr-x  7 oracle dba  4096 Apr  7 11:04 racg
drwxr-xr-x 13 oracle dba  4096 Apr  7 11:08 rdbms
drwxr-xr-x  4 oracle dba  4096 Apr  7 11:02 relnotes
-rwxr-x---  1 oracle dba   458 Apr  7 11:08 root.sh
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:50 scheduler
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 slax
drwxr-xr-x 13 oracle dba  4096 Apr  7 11:00 sqldeveloper
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 sqlj
drwxr-xr-x  7 oracle dba  4096 Apr  7 11:07 sqlplus
drwxr-xr-x 10 oracle dba  4096 Apr  7 11:04 srvm
drwxr-xr-x 15 oracle dba  4096 Apr  7 11:04 sysman
drwxr-xr-x  2 oracle dba  4096 Apr  7 10:58 timingframework
drwxr-xr-x  3 oracle dba  4096 Apr  7 11:02 ucp
drwxr-xr-x  4 oracle dba  4096 Apr  7 10:50 uix
drwxr-xr-x  2 oracle dba  4096 Apr  7 10:54 utl
drwxr-xr-x  3 oracle dba  4096 Apr  7 10:50 wwg
drwxr-xr-x  7 oracle dba  4096 Apr  7 10:55 xdk
[oracle@vmcentos 11.2]$ pwd
/u02/EUCARIS/eucarisdb/11.2
[oracle@vmcentos 11.2]$

4. clone.pl

Now it is time to assign EUCARIS.env (new environement script from target server) which will define all needed for next steps.

Because we have moved files to different directory and different server, many Oracle paths are wrong. Beside that many libraries point to invallid place what will prevent Oracle form normal functioning. So this will be fixed with clone.pl script, which is placed in $ORACLE_HOME/clone/bin directory:
[oracle EUCARIS@vmcentos ~]$ cd /u02/EUCARIS/eucarisdb/11.2/clone/bin
[oracle EUCARIS@vmcentos ~]$ pwd
/u02/EUCARIS/eucarisdb/11.2/clone/bin
[oracle EUCARIS@vmcentos bin]$ perl clone.pl ORACLE_HOME=$ORACLE_HOME ORACLE_BASE=$ORACLE_BASE
./runInstaller -clone -waitForCompletion  "ORACLE_HOME=/u02/EUCARIS/eucarisdb/11.2" "ORACLE_BASE=/u02" -defaultHomeName -silent -noConfig -nowait
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4031 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-07-09_10-45-46AM. Please wait ...Oracle Universal Installer, Version 11.2.0.1.0 Pr   oduction
Copyright (C) 1999, 2009, Oracle. All rights reserved.

You can find the log of this install session at:
 /oraInventory/logs/cloneActions2010-07-09_10-45-46AM.log
.................................................................................................... 100% Done.



Installation in progress (Friday, July 9, 2010 10:46:21 AM GMT-01:00)
..............................................................................                                                  78% Done.
Install successful

Linking in progress (Friday, July 9, 2010 10:46:37 AM GMT-01:00)
Link successful

Setup in progress (Friday, July 9, 2010 10:48:43 AM GMT-01:00)
Setup successful

End of install phases.(Friday, July 9, 2010 10:54:04 AM GMT-01:00)
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.
--------------------------------------
The "/u02/EUCARIS/eucarisdb/11.2/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file    may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing t   he same.
The "/u02/EUCARIS/eucarisdb/11.2/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file    may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the    same.

--------------------------------------
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u02/EUCARIS/eucarisdb/11.2/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The cloning of OraHome1 was successful.
Please check '/oraInventory/logs/cloneActions2010-07-09_10-45-46AM.log' for more details.
[oracle EUCARIS@vmcentos bin]$

5. root.sh script

In "/oraInventory/logs/cloneActions2010-07-09_10-45-46AM.log" file, at the very end, there is a notice that you have to run in new ORACLE_HOME (/u02/EUCARIS/eucarisdb/11.2/) as root "root.sh" script.
[root@vmcentos 11.2]# sh /u02/EUCARIS/eucarisdb/11.2/root.sh
Check /u02/EUCARIS/eucarisdb/11.2/install/root_vmcentos_2010-07-09_10-58-32.log for the output of root script
[root@vmcentos 11.2]#
Check log /u02/EUCARIS/eucarisdb/11.2/install/root_vmcentos_2010-07-09_10-58-32.log
[root@vmcentos 11.2]# cat /u02/EUCARIS/eucarisdb/11.2/install/root_vmcentos_2010-07-09_10-58-32.log
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u02/EUCARIS/eucarisdb/11.2
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
All looks good!

6. Check oraInventory

Because we want cloned ORACLE_HOME to act as any other "normaly" created ORACLE_HOME, it should be properly registered in Oracle Inventory. So let us check it's content. In mine case oraInventory was located:
[oracle EUCHARIS@vmcentos ~]$ cat /oraInventory/oraInst.loc
inventory_loc=/oraInventory
inst_group=dba
So let us check main file inventory.xml:
[oracle EUCHARIS@vmcentos ContentsXML]$ cat /oraInventory/ContentsXML/inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2009, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
  <SAVED_WITH>11.2.0.1.0</SAVED_WITH>
  </MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/UAT/uatdb/11.2" TYPE="O" IDX="1"/>
<HOME NAME="OraHome1" LOC="/u02/EUCARIS/eucarisdb/11.2" TYPE="O" IDX="2"/>
</HOME_LIST>
</INVENTORY>
As you see beside UAT database, which was on target server before clonig (ORACLE_HOME_NAME="OraDb11g_home1") we have successfully added cloned ORACLE_HOME with ORACLE_HOME_NAME="OraHome1". This name is default because we didn't set it in clone.pl script parameter.

The end

One of the most important characteristic, of cloned ORACLE_HOME in explained way, is that it can be done on the same server as well. Only one path has to be different.

And the best of all, while source is cloned all database inside source ORACLE_HOME may be up and running without any problems what give us possibility to clone any production whenever we want.

Cheers!

Friday, July 2, 2010

New blog design

Life bring us changes (eppur si muove).

So I decided to change look and feel of mine blog to more "rounded" rectangles with pastel colors. Hope you'll find it interested and more relaxing as I am.
;-)

Cheers!

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign