Tuesday, March 15, 2011

Multiset Union ALL operation on NESTED tables built on OBJECT Types

This blog is to share some difficulties when we try to use MULTISET UNION ALL command on NESTED TABLES
built on OBJECT TYPES.In our example the nested table test_obj_array is built on a type called test_obj.

I will then mention a small workaround which is not perfect but workable by creating a map member function

SQL> select col1 from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya

SQL> create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100));
  2  /

Type created.

SQL> create type test_obj_array as table of test_obj;
  2  /

Type created.



SQL>  select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

It gets more complicated when we use MULTISET UNION ALL command when are dealing with NESTED TABLES built on OBJECT TYPES

SQL> DECLARE
  2   x test_obj_array := test_obj_array();
  3   y test_obj_array := test_obj_array();
  4   z test_obj_array := test_obj_array();
  5  BEGIN
  6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
  7     INTO x from dual;
  8  y:=x;
  9  z:=x multiset union all y;
 10 
 11  END;
 12  /
z:=x multiset union all y;
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2079; Type
0x87ffffffbf3eb9a0 has no MAP method.; _anon__c0000000381e5be8__AB[9, 1]]

SQL> drop type test_obj_array;

Type dropped.

SQL> drop type test_obj;

Type dropped.

create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100),map member function test_function return varchar2);
/

create type test_obj_array as table of test_obj;
/


SQL> create or replace type body test_obj
  2  as
  3  map member function test_function return varchar2 is
  4  begin
  5   return col1||col2||col3;
  6  end;
  7  end;
  8  /

Type body created.


SQL>  select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

SQL> DECLARE
  2   x test_obj_array := test_obj_array();
  3   y test_obj_array := test_obj_array();
  4   z test_obj_array := test_obj_array();
  5  BEGIN
  6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
  7     INTO x from dual;
  8  y:=x;
  9  z:=x multiset union all y;
 10  for i in 1 .. z.count() loop dbms_output.put_line(z(i).col1); end loop;
 11 
 12  END;
 13  /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>
Posted by at No comments:

Monday, March 14, 2011

Shell Script to map datafiles on source to target filesystem before doing RMAN duplicate/restore

Many a times we DBA's are asked to refresh the DEV/QA database with PROD.
The biggest headache is rarely do the file system names match and even if they match the size varies and therefore we can rarely do a 1 to 1 mapping.

For example , a DB file /oracle/oradata01/proddb/data/users01.dbf might have to go to /oracle/oradata02 filesystem as either there might not be a filesystem called /oracle/oradata01 on the dev/qa box or it might be too small to accomodate this file after the other datafiles go there.

Therefore I have developed a utility called mapper.sh (a shell script written in ksh) which does this very nicely.

This utility has 2 arguments
Parameter 1 to shell script- Mandatory- name of the file which has information about the file system on destination server (1st field), file size in MB (2nd field) and Utilization factor in % (optional 3rd field).
For example
/oracle/oradata01/devdb/data 10000 72
/oracle/oradata02/devdb/data 5000
This means our candidates on the dev/qa box are
1./oracle/oradata01/devdb/data which has 10000M free space but we want to use only 72% of it.
2./oracle/oradata02/devdb/data which has 5000M but no utilization factor is mentioned.That means the tool will take a default utilization factor of 90% unless it is over-rided by the 2nd parameter to the shell script which is explained just below.

Parameter 2 to shell script -
this is an optional parameter which takes effect for file systems (inside the file of parameter 1) which have the 3rd field missing.For example in the above file /oracle/oradata02/devdb/data file system will use this parameter (if passed) or if not passed as 2nd argument will use the default 90% utilization factor.

Things might be a bit confusing but let us jump to the demo shown below which will make things clearer
**caution**PLEASE  CHECK THIS SCRIPT IN A TEST ENV BEFORE IMPLEMENTING
At the production DB server ,login as DBA account and check the datafile sizes

SQL> select file_name||'->'||(bytes/1024/1024)bytes_in_mb from dba_data_files;

/oracle/oradata01/prodpr/data/system01.dbf->542
/oracle/oradata02/prodpr/data/undotbs01.dbf->1094
/oracle/oradata02/prodpr/data/sysaux01.dbf->500
/oracle/oradata01/prodpr/data/drsys01.dbf->1024
/oracle/oradata01/prodpr/data/maxdata01.dbf->5049.5
/oracle/oradata02/prodpr/data/users01.dbf->1024

SQL> select sum(bytes/1024/1024) from dba_data_files;

SUM(BYTES/1024/1024)
--------------------
              9233.5

SQL>

Assume that we already know the destination server file systems, here is my input file to
the shell script (/tmp/destfs.txt).

contents of /tmp/destfs.txt
==================cut from below==============
/oracle/oradata01/testdv/data 100000 2
/oracle/oradata02/testdv/data 5000 80
/oracle/oradata03/testdv/data 8000
=================cut from above===============

What we are telling the tool is

1.utilize just 2% of the /oracle/oradata01/testdv/data which is almost 100G(approx)i.e use just 2G
2.use 80% of /oracle/oradata02/testdv/data i.e.use 4G out of 5G
3.Since we don't mention anything here it takes the 2nd parameter to the script (if passed) or
if nothing is passed takes default 90% of available 8G.

Demo:

homelinux<oracle>/tmp> cat /tmp/destfx.txt
/oracle/oradata01/testdv/data 100000 2
/oracle/oradata02/testdv/data 5000 80
/oracle/oradata03/testdv/data 8000


homelinux<oracle>/tmp>
homelinux<oracle>/tmp>
homelinux<oracle>/tmp>
homelinux<oracle>/tmp>
homelinux<oracle>/tmp>

Before running please make sure mapper.sh has execute privs and all the oracle env's have been set
as the below script tries to connect as sysdba using the unix account it is being run on.
therefore I recommend running this as oracle user or a unix account in DBA group.

homelinux<oracle>/tmp> ./mapper.sh /tmp/destfx.txt 87
Processing ...
...


Utlization Percentage specified by user as 87
This might change at individual file system level if specified in /tmp/destfx.txt
.....
Utilization limit for /oracle/oradata01/testdv/data is 2
....
.....
Utilization limit for /oracle/oradata02/testdv/data is 80
....
.....
Utilization limit for /oracle/oradata03/testdv/data is 87
....
Sucessfully allocated destination file system to all data files
SET NEWNAME FOR DATAFILE 5 TO '/oracle/oradata03/testdv/data/maxdata0101.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oracle/oradata03/testdv/data/undotbs0101.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/oracle/oradata02/testdv/data/users0101.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/oracle/oradata02/testdv/data/drsys0101.dbf';
SET NEWNAME FOR DATAFILE 1 TO '/oracle/oradata03/testdv/data/system0101.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/oracle/oradata02/testdv/data/sysaux0101.dbf';
****
Above information is stored in /tmp/datafile_map.log
Please refer to tables - file_placement and dest_fsize_tab for other useful information
homelinux<oracle>/tmp>


Let us see the output if the o/p file which can be copied and pasted
into our rman duplicate /restore file
homelinux<oracle>/tmp> cat /tmp/datafile_map.log
SET NEWNAME FOR DATAFILE 5 TO '/oracle/oradata03/testdv/data/maxdata0101.dbf'; 
SET NEWNAME FOR DATAFILE 2 TO '/oracle/oradata03/testdv/data/undotbs0101.dbf'; 
SET NEWNAME FOR DATAFILE 6 TO '/oracle/oradata02/testdv/data/users0101.dbf';   
SET NEWNAME FOR DATAFILE 4 TO '/oracle/oradata02/testdv/data/drsys0101.dbf';   
SET NEWNAME FOR DATAFILE 1 TO '/oracle/oradata03/testdv/data/system0101.dbf';  
SET NEWNAME FOR DATAFILE 3 TO '/oracle/oradata02/testdv/data/sysaux0101.dbf';  
homelinux<oracle>/tmp>


Please note 2 tables called file_placement and dest_fsize_tab are created in the DB account
which can be dropped or left around which will be re-used the next time the script is run.


SOURCE CODE FOR MAPPER.SH
*************************************************

#/bin/ksh
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++
#+++++++ AUTHOR :GAUTHAM CHANDRASEKARAN
#File name: mapper.sh
#mapper.sh <file name> [ <optional utilization capacity>
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++

echo "Processing ..."
echo "..."
echo
echo
if [ $# -eq 2 ]
then
 DEF_UTIL_PCT=$2
 echo "Utlization Percentage specified by user as $DEF_UTIL_PCT"
 echo "This might change at individual file system level if specified in $1"
elif [ $# -eq 1 ]
then
 DEF_UTIL_PCT="90"
 echo "Utlization Percentage assumed as default 90%"
 echo "This might change at individual file system level if specified in $1"
else
 echo "ERROR *** Wrong number of arguments to $0"
 echo "Usage : $0 <filename>  [ <utilization_pct> ]"
 exit 1
fi

if [ ! -f "$1" ]
then
 echo "ERROR *** File $1 does not exist"
 exit 1
fi

sqlplus -s /nolog <<EOF
connect / as sysdba;
set echo off;
set feedback off;
DROP TABLE dest_fsize_tab
/
CREATE TABLE dest_fsize_tab
(dir_name VARCHAR2(200) primary key,
full_size NUMBER(10),
avbl_size NUMBER(10),
utilization_pct NUMBER(3))
/
DROP TABLE file_placement
/
CREATE TABLE file_placement
(file_id NUMBER(5),
file_name VARCHAR2(50),
dir_name VARCHAR2(200),
sequence_num NUMBER(3))
/

CREATE OR REPLACE PACKAGE pk_genutilitypkg
  AS
     /*
     Generic String Parser: provide a delimiter and it returns an
     index-by table of the individual elements of the string that are
     separated by the specified delimiter.
     Author: "GAUTHAM CHANDRASEKARAN" <gautha@hotmail.com>
     */
    TYPE t_string IS TABLE OF VARCHAR2(2000)
       INDEX BY BINARY_INTEGER;
    m_ctr NUMBER(5);
    m_pos NUMBER(5);
    PROCEDURE sp_parsestring (
       p_string IN VARCHAR2,
       delimiter IN VARCHAR2,
       p_t_string OUT t_string);
 END pk_genutilitypkg;
 /
 CREATE OR REPLACE PACKAGE BODY pk_genutilitypkg
 AS
    PROCEDURE sp_parsestring (
         p_string IN VARCHAR2,
         delimiter IN VARCHAR2,
         p_t_string OUT t_string)
      IS
         m_string VARCHAR2(4000);
      BEGIN
         /* Raise a Error if the length of the delimiter is not 1 */
         IF LENGTH (delimiter) != 1
         THEN
            raise_application_error (-20001,
                'Delimiter should be of only one character');
            RETURN;
         END IF;
         m_string := p_string;
         m_ctr := 1;
         LOOP
            m_pos := INSTR (m_string, delimiter);
            IF m_pos > 1
            THEN
               p_t_string (m_ctr) := SUBSTR (m_string, 1, m_pos - 1);
               IF (m_pos < LENGTH (m_string))
               THEN
                  m_string := SUBSTR (
                                 m_string,
                                 m_pos + 1,
                                 LENGTH (m_string) - m_pos
                              );
               ELSIF m_pos = LENGTH (m_string)
               THEN
                  m_ctr := m_ctr + 1;
                  p_t_string (m_ctr) := NULL;
                  EXIT;
               END IF;
            ELSIF m_pos = 1
            THEN
               p_t_string (m_ctr) := NULL;
               IF m_pos < LENGTH (m_string)
               THEN
                  m_string := SUBSTR (
                                 m_string,
                                 m_pos + 1,
                                 LENGTH (m_string) - m_pos
                              );
               ELSIF m_pos = LENGTH (m_string)
               THEN
                  m_ctr := m_ctr + 1;
                  p_t_string (m_ctr) := NULL;
                  EXIT;
               END IF;
            ELSIF m_pos = 0
            THEN
               p_t_string (m_ctr) := m_string;
               EXIT;
            END IF;
            m_ctr := m_ctr + 1;
         END LOOP;
      END;
END pk_genutilitypkg;
 /
exit;
EOF

cat $1| while read INPUT_LINE
do
FIELD_CTR=`echo $INPUT_LINE|awk '{print NF}'`
if [ $FIELD_CTR -eq 3 ]
then
  DEST_NAME=`echo $INPUT_LINE|awk '{print $1}'`
  DEST_SIZE=`echo $INPUT_LINE|awk '{print $2}'`
  UTIL_PCT=`echo $INPUT_LINE|awk '{print $3}'`
  echo "....."
  echo "Utilization limit for $DEST_NAME is $UTIL_PCT"
  echo "...."
sqlplus -s /nolog<<EOF
connect / as sysdba;
set feedback off
whenever sqlerror exit sql.sqlcode;
INSERT INTO dest_fsize_tab  values ('$DEST_NAME', $DEST_SIZE,$DEST_SIZE, $UTIL_PCT);
exit;
EOF

if [ $? -ne 0 ]
then
 echo "***Error while inserting into configuration tables from $1"
 echo "Check entries in $1 : $DEST_NAME $DEST_SIZE $UTIL_PCT"
 exit 1
fi
elif [ $FIELD_CTR -eq 2 ]
then

DEST_NAME=`echo $INPUT_LINE|awk '{print $1}'`
  DEST_SIZE=`echo $INPUT_LINE|awk '{print $2}'`
  echo "....."
    echo "Utilization limit for $DEST_NAME is $DEF_UTIL_PCT"
  echo "...."
 
sqlplus -s /nolog<<EOF
connect / as sysdba;
set feedback off
whenever sqlerror exit sql.sqlcode;
INSERT INTO dest_fsize_tab  values ('$DEST_NAME', $DEST_SIZE,$DEST_SIZE, $DEF_UTIL_PCT);
exit;
EOF
if [ $? -ne 0 ]
then
 echo "***Error while inserting into configuration tables from $1"
 echo "Check entries in $1 : $DEST_NAME $DEST_SIZE $DEF_UTIL_PCT"
 exit 1
fi

else
 echo "Please check the contents of $1 file"
 echo "Wrong number of entries inside file"
 exit 1
fi

done


sqlplus -s /nolog <<EOF
connect / as sysdba;
set feedback off;

set serverout on
whenever sqlerror exit ;
DECLARE
x pk_genutilitypkg.t_string;
v_sequence_num file_placement.sequence_num%TYPE;
formatted_fname pk_genutilitypkg.t_string;
v_dir_name dest_fsize_tab.dir_name%TYPE ;

cursor c1
is select file_id, file_name, (bytes/1024)/1024 fsize from dba_data_files
order by fsize desc;

BEGIN

FOR c2 in c1
LOOP
   BEGIN
   SELECT t1.dir_name
   INTO v_dir_name
   FROM
   (SELECT dir_name
   FROM   dest_fsize_tab
   WHERE  ( (avbl_size-c2.fsize)*100/full_size) >=(100-utilization_pct)
   order by avbl_size) t1
   where rownum<2 ;
  
   EXCEPTION
   WHEN no_data_found THEN
   raise_application_error(-20001,'No File System capable of accomodating '||c2.file_name);
   END;
   pk_genutilitypkg.sp_parsestring (c2.file_name,'/',x) ;
   pk_genutilitypkg.sp_parsestring(x(x.count),'.',formatted_fname);
   SELECT NVL(max(sequence_num),0)+1
   INTO v_sequence_num
   FROM file_placement
   WHERE file_name =formatted_fname(1);

   INSERT INTO file_placement
   VALUES
   (c2.file_id,
   formatted_fname(1),
   v_dir_name,
   v_sequence_num);
  
   UPDATE dest_fsize_tab
   SET  avbl_size=avbl_size-c2.fsize
   WHERE dir_name=v_dir_name;
   commit;
  
  
END LOOP;
dbms_output.put_line('Sucessfully allocated destination file system to all data files');

END;
/
whenever sqlerror exit 1;
EOF

if [ $? -ne 0 ]
then
 echo "Please allocate more space or try increasing Utilization Percentage"
 exit 1
fi

sqlplus -s /nolog<<EOF
connect / as sysdba;
set feedback off
set verify off
set pagesize 0
set heading off
spool /tmp/datafile_map.log
select 'SET NEWNAME FOR DATAFILE '||file_id||' TO '''||dir_name||'/'||file_name||trim(to_char(sequence_num,'00'))||'.dbf'';' from file_placement;
spool off
EOF

#cat /tmp/datafile_map.log
echo "****"
echo "Above information is stored in /tmp/datafile_map.log"
echo "Please refer to tables - file_placement and dest_fsize_tab for other useful information"
Posted by at No comments:

Break comma-separed values in a table into rows

In this blog, I am going to talk  about an interesting way to split a comma-separated list into individual rows.

To achieve this consider a table called junk_tab with a column called names .

SQL> select names from junk_tab;

NAMES
--------------------------------------------------------------------------------
gautham,siddhu,surya
frank,shriram,vimal

SQL> desc junk_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAMES                                              VARCHAR2(4000 CHAR)

What i intend to do is to split the individual names (delimited by comma) into separate rows.

Therefore at the end of the exercise we will have a select query returning 6 rows as
gautham
siddhu
..
..
..
vimal

6 rows selected

There are many ways to achieve this and infact there are several ways to achieve the same end result.

I am going to teach you step by step about what I wanted to do .

Step 1
======

For every row in the table I want to get the number of comma-separated values.

Here to get this the logic is subtract the length of the string after the comma's have ben stripped off from the original
length of the string .The result will give you the count of the commas.Adding 1 to it will give you the number of
comma-separated variables.

SQL> select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab;

NAMES
----------------------------------------------------------------------------------------------------
       XXX
----------
gautham,siddhu,surya
         3

frank,shriram,vimal
         3

Step 2
=======
The below sql is used for generating a sequence of number starting from 1 till what we specify.

SQL> select level num from dual connect by level <=5;

       NUM
----------
         1
         2
         3
         4
         5
        
In the below query, note the "with tmp as" I have used to include the above query as a temporary view.
        
Now do the following

SQL> with tmp as ( select level num from dual connect by level <= 100 )
  2  select test123.names,tmp.num
  3  from
  4  (select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab) test123,tmp
  5  where tmp.num <=test123.xxx
  6  order by test123.names,tmp.num
  7  /

NAMES
----------------------------------------------------------------------------------------------------
       NUM
----------
frank,shriram,vimal
         1

frank,shriram,vimal
         2

frank,shriram,vimal
         3


NAMES
----------------------------------------------------------------------------------------------------
       NUM
----------
gautham,siddhu,surya
         1

gautham,siddhu,surya
         2

gautham,siddhu,surya
         3
        
Step 3
+++++++++

This is easy as we just have to extract the 1st string from row 1, 2nd string from row 2 etc.



SQL> with tmp as ( select level num from dual connect by level <= 100 )
  2  select
  3  substr(test123.names ,
  4    decode(tmp.num,1,1,(instr(test123.names,',',1,(tmp.num-1))+1)),
  5    decode(instr(test123.names,',',1,tmp.num),0,length(test123.names)+1,instr(test123.names,',',1
,tmp.num)) - decode(tmp.num,1,1,(instr(test123.names,',',1,(tmp.num-1))+1))
  6    )
  7  from
  8  (select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab) test123,tmp
  9  where tmp.num <=test123.xxx
 10  order by test123.names,tmp.num
 11  /

SUBSTR(TEST123.NAMES,DECODE(TMP.NUM,1,1,(INSTR(TEST123.NAMES,',',1,(TMP.NUM-1))+1)),DECODE(INSTR(TES
----------------------------------------------------------------------------------------------------
frank
shriram
vimal
gautham
siddhu
surya

6 rows selected.

SQL>


6 rows selected.
Posted by at No comments:

Pipelined-Functions - Practical Use

Pipeline functions allow us to call them from a select statement and can be used to return more than 1 row.
The below example would make it more clear.
Say we have a table called employee and the fields are (or rather the most significant fields for our discussion) are
ename and dno .

SQL> select substr(ename,1,25),dno from employee;

SUBSTR(ENAME,1,25)               DNO
------------------------- ----------
JACK                              10
JILL                              10
MASON                             20
MARK                              20


you are being asked to write a small pl/sql code to return the enames of a particular department, the normal way we would
do is to write a procedure like

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)
/

CREATE or replace procedure p1(dno_p IN number,ename_p OUT vcarray)
IS
CURSOR c1 is select ename from employee where dno=dno_p;
i number:=1;
BEGIN
ename_p := vcarray();
for c2 in c1
loop
 ename_p.extend;
 ename_p(ename_p.count) :=c2.ename ;
 i:=i+1;
end loop;
END;
/

To call the above procedure , we are forced to write a pl/sql block like

SQL> declare
  2  ename vcarray ;
  3  begin
  4  p1(10,ename);
  5  for i in 1..ename.COUNT
  6  LOOP
  7  dbms_output.put_line(ename(i));
  8  end loop;
  9  end;
 10  /
JACK
JILL

PL/SQL procedure successfully completed.

So in the above example we are forced to write a pl/sql block to read from the procedure.

The below example uses pipelined functions which will make it possibel for the same
job to be done via a function (eventhough we think of function as only returning
a single value) and the icing on the cake is the function can be referenced as a simple select
from sqlplus prompt itself....




CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)
/

CREATE OR REPLACE FUNCTION get_employees (dno_p IN number)
RETURN vcarray
PIPELINED
AS

CURSOR c1 is select ename from employee where dno=dno_p;
BEGIN

FOR c2 in c1
LOOP
   pipe row (c2.ename);
END LOOP;
RETURN;
END;
/


SQL> select * from TABLE (get_employees(10) );

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
JACK
JILL
Posted by at No comments:

Sunday, March 13, 2011

Calling Shell Scripts from Oracle 10gR2

The user requirement was to run a database export on demand from the click of a web browser button and they wanted to hide the nitty gritties of the database export except
Specifying the dump file name , ftp server name etc.

We decided to use the oracle 10g feature of invoking a shell script(which does all the above tasks) from dbms_scheduler built-in package ( which has the capability to call shell scripts and can even pass
command line arguments to them).

Here is the sample code for proof of concept .

contents of /tmp/junk.sh (shell script which takes in 1 argument)
+++++++++++++++++++++++++++++++++++++++++++++++++++
#!/bin/ksh
touch /tmp/$1


contents of the stored procedure which calls dbms_scheduler
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

create or replace procedure p1(abc varchar2)
is
begin


DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'TEST123',
program_type => 'EXECUTABLE',
program_action => '/tmp/junk.sh',
number_of_arguments => 1,
enabled => FALSE,
comments => 'Test Shell Script'
);

DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
program_name => 'TEST123',
argument_position => 1,
argument_type => 'VARCHAR2'
);

DBMS_SCHEDULER.create_job(job_name => 'TEST_JOB',
program_name => 'TEST123',
start_date => sysdate,
auto_drop => FALSE,
comments => 'testing 123');

dbms_scheduler.set_job_argument_value('TEST_JOB',1,abc);

DBMS_SCHEDULER.enable(NAME => 'TEST123');
DBMS_SCHEDULER.enable(NAME => 'TEST_JOB');

DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB', use_current_session => TRUE);



DBMS_SCHEDULER.drop_job(job_name => 'TEST_JOB');
DBMS_SCHEDULER.DROP_PROGRAM (program_name => 'TEST123');


commit;

end;
/

By executing procedure p1 , I was able to successfully run a shell script and also pass a argument to it.

The only hitch is the shell script is run as an OS user called nobody and to change it please read the following link
http://forums.oracle.com/forums/thread.jspa?messageID=1748530

Hope this was useful to you.
Posted by at No comments:

Multiset Union ALL operation on NESTED tables built on OBJECT Types

This blog is to share some difficulties when we try to use MULTISET UNION ALL command on NESTED TABLES
built on OBJECT TYPES.In our example the nested table test_obj_array is built on a type called test_obj.

I will then mention a small workaround which is not perfect but workable by creating a map member function

SQL> select col1 from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya

SQL> create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100));
  2  /

Type created.

SQL> create type test_obj_array as table of test_obj;
  2  /

Type created.



SQL>  select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

It gets more complicated when we use MULTISET UNION ALL command when are dealing with NESTED TABLES built on OBJECT TYPES

SQL> DECLARE
  2   x test_obj_array := test_obj_array();
  3   y test_obj_array := test_obj_array();
  4   z test_obj_array := test_obj_array();
  5  BEGIN
  6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
  7     INTO x from dual;
  8  y:=x;
  9  z:=x multiset union all y;
 10
 11  END;
 12  /
z:=x multiset union all y;
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2079; Type
0x87ffffffbf3eb9a0 has no MAP method.; _anon__c0000000381e5be8__AB[9, 1]]

SQL> drop type test_obj_array;

Type dropped.

SQL> drop type test_obj;

Type dropped.

create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100),map member function test_function return varchar2);
/




SQL> create or replace type body test_obj
  2  as
  3  map member function test_function return varchar2 is
  4  begin
  5   return col1||col2||col3;
  6  end;
  7  end;
  8  /

Type body created.

create type test_obj_array as table of test_obj;
/


SQL>  select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

SQL> DECLARE
  2   x test_obj_array := test_obj_array();
  3   y test_obj_array := test_obj_array();
  4   z test_obj_array := test_obj_array();
  5  BEGIN
  6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
  7     INTO x from dual;
  8  y:=x;
  9  z:=x multiset union all y;
 10  for i in 1 .. z.count() loop dbms_output.put_line(z(i).col1); end loop;
 11
 12  END;
 13  /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>
Posted by at No comments:

Cast/Multiset/Multiset Union Operations using Object Types

 In this blog,I have given a small example of how to transfer the contents of a table column into a nested table and then print the contents out using CAST /MULTISET operation.

Also checkout the MULTISET UNION command I have used to merge 2 nested tables.

SQL> create table junk_tab(col1 varchar2(100));

Table created.

SQL> insert into junk_tab values('&1');
Enter value for 1: gautham
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('gautham')

1 row created.

SQL> /
Enter value for 1: siddhu
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('siddhu')

1 row created.

SQL> /
Enter value for 1: surya
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('surya')

1 row created.

SQL> select * from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya
SQL> create type string_tab as table of varchar2(100);
  2  /

Type created.

SQL> desc string_tab
 string_tab TABLE OF VARCHAR2(100 CHAR)
SQL> select cast(multiset(select col1 from junk_tab) as string_tab) from dual;

CAST(MULTISET(SELECTCOL1FROMJUNK_TAB)ASSTRING_TAB)
--------------------------------------------------------------------------------
STRING_TAB('gautham', 'siddhu', 'surya')


SQL> declare
  2     x string_tab := string_tab();
  3      y string_tab := string_tab();
  4 
  5      z string_tab := string_tab();
  6      begin
  7      select cast(multiset(select col1 from junk_tab) as string_tab) into x from dual;
  8      y := x;
  9 
 10      z := x multiset union all y;
 11  for i in 1 .. z.count()
 12  loop
 13  dbms_output.put_line(z(i));
 14 
 15  end loop;
 16  end;
 17  /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

One more way to use multiset union all --
SQL> declare
  2     x string_tab := string_tab('gautham');
  3      y string_tab := string_tab('siddhu','surya');
  4 
  5      z string_tab := string_tab();
  6      begin
  7     z:=x multiset union all y;
  8  for i in 1 .. z.count()
  9  loop
 10  dbms_output.put_line(z(i));
 11 
 12  end loop;
 13  end;
 14  /
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>
Posted by at No comments:

Friday, March 11, 2011

Reading Oracle Results in a UNIX Shell script

Usually most developers working with Oracle databases on UNIX platform would have some point in their "coding" life felt the need to read Oracle results into a shell variable.

Here is one of the many ways to accomplish this -

The below example required getting the directory path into a UNIX shell variable to perform some OS related maintenance operations.

DATA_PUMP_DIR="TEST_DIR"
DIR_PATH=`sqlplus -s ${DEST_DB_ADMIN}/${DEST_DB_ADMIN_PWD} <<EOF
set heading off
set pagesize 0
set feedback off
set verify off
select directory_path from dba_directories where directory_name='$DATA_PUMP_DIR';
exit;
EOF`
echo $DIR_PATH
Posted by at No comments:

Thursday, March 10, 2011

Splitting delimiter separated list into pl/sql table

I developed this code in 1999 when I used to code pl/sql for a living.
We used to have an order-entry system where the customer used to order multiple items and each item along with quantity used to sit as a row in the order detail table.
Our team wanted to avoid making multiple calls to the database for each order item.Therefore we told the UI folks to send us the customer "ordered" items as a single string list separated by a de-limiter and inside the procedure we would split the individual order items into a pl/sql table and then insert it into the order detail table.
I believe oracle has an in-built package to achieve the same in dbms_utility but I believe the below code is more robust in the sense that this could handle NULL values too.

For example if somebody passed  the following string (with * delimiter)

abc*xyz**mno

My procedure would break this up into

1.abc
2.xyz
3.NULL
4.mno

Likewise something like *abc**cvs* would be broken up as
1.NULL
2.abc
3.NULL
4.cvs
5.NULL

Here is the code to achieve the same.....



 CREATE OR REPLACE PACKAGE pk_genutilitypkg
  AS
     /*
     Generic String Parser: provide a delimiter and it returns an
     index-by table of the individual elements of the string that are
     separated by the specified delimiter.
     Author: "GAUTHAM CHANDRASEKARAN" <gautha@hotmail.com>
     */
    TYPE t_string IS TABLE OF VARCHAR2(2000)
       INDEX BY BINARY_INTEGER;
    m_ctr NUMBER(5);
    m_pos NUMBER(5);
    PROCEDURE sp_parsestring (
       p_string IN VARCHAR2,
       delimiter IN VARCHAR2,
       p_t_string OUT t_string);
END pk_genutilitypkg;
/

CREATE OR REPLACE PACKAGE BODY pk_genutilitypkg
 AS
    PROCEDURE sp_parsestring (
       p_string IN VARCHAR2,
       delimiter IN VARCHAR2,
       p_t_string OUT t_string)
    IS
       m_string VARCHAR2(4000);
    BEGIN
       /* Raise a Error if the length of the delimiter is not 1 */
       IF LENGTH (delimiter) != 1
       THEN
          raise_application_error (-20001,
              'Delimiter should be of only one character');
          RETURN;
       END IF;
       m_string := p_string;
       m_ctr := 1;
       LOOP
          m_pos := INSTR (m_string, delimiter);
          IF m_pos > 1
          THEN
             p_t_string (m_ctr) := SUBSTR (m_string, 1, m_pos - 1);
             IF (m_pos < LENGTH (m_string))
             THEN
                m_string := SUBSTR (
                               m_string,
                               m_pos + 1,
                               LENGTH (m_string) - m_pos
                            );
             ELSIF m_pos = LENGTH (m_string)
             THEN
                m_ctr := m_ctr + 1;
                p_t_string (m_ctr) := NULL;
                EXIT;
             END IF;
          ELSIF m_pos = 1
          THEN
             p_t_string (m_ctr) := NULL;
             IF m_pos < LENGTH (m_string)
             THEN
                m_string := SUBSTR (
                               m_string,
                               m_pos + 1,
                               LENGTH (m_string) - m_pos
                            );
             ELSIF m_pos = LENGTH (m_string)
             THEN
                m_ctr := m_ctr + 1;
                p_t_string (m_ctr) := NULL;
                EXIT;
             END IF;
          ELSIF m_pos = 0
          THEN
             p_t_string (m_ctr) := m_string;
             EXIT;
          END IF;
          m_ctr := m_ctr + 1;
       END LOOP;
    END;
END pk_genutilitypkg;
/


declare
x pk_genutilitypkg.t_string;
str1 varchar2(200):='121,234,345,657,890';
begin
pk_genutilitypkg.sp_parsestring(str1,',',x);

for i in 1..x.count
loop
dbms_output.put_line(x(i));

end loop;
end;
/






SQL> declare
  2  x pk_genutilitypkg.t_string;
  3  str1 varchar2(200):='121,234,345,657,890';
  4  begin
  5  pk_genutilitypkg.sp_parsestring(str1,',',x);
  6 
  7  for i in 1..x.count
  8  loop
  9  dbms_output.put_line(x(i));
 10 
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> /
121
234
345
657
890

PL/SQL procedure successfully completed.
Posted by at No comments:

Dataguard - Performing RMAN backup from standby site and test recovery from standby backup

I wanted to share a small piece of work I had done for one of my earlier employers who had asked me to check the viability of taking RMAN backups on a standby site and test a recovery scenario of a full database wipe-out scenario.



PRIMARY - STANDBY SETUP
==============

1)
Primary Database : orioles
 Server Name: patty
Backup location at Primary :  /mnt/xserve-raid2/u05/bkp (same as in standby)

2)
Standby Database: orioles
Server Name : selma
Backup location at Standby :  /mnt/xserve-raid2/u05/bkp (same as in primary)

2a) Catalog instance is rmancat and the catalog owner is rman_owner

3)
Initorioles.ora (Primary) at patty

I have only pasted the relevant standby parameters

####################################
#Standby Parameters
####################################
log_archive_dest_2='SERVICE=orioles_standby ARCH OPTIONAL'
standby_file_management=auto #Allow rename, add, drop a data file
#remote_archive_enable=TRUE # deprecated

LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE

#log_parallelism=1 # obsolete in 10g

###########################################
# ARchive logging
###########################################
 log_archive_dest_1='LOCATION=/mnt/xserve-raid/u03/oradata/orioles/arch'
 log_archive_format='%t_%s_%r.arc'
 #log_archive_start=FALSE # deprecated in 10g
 log_archive_max_processes=9

db_recovery_file_dest=''
dg_broker_config_file1=''
dg_broker_config_file2=''

4)
Initorioles.ora (Standby) at Selma (only standby parameters)

Please note FAL_SERVER and FAL_CLIENT corresponds to tnsnames.ora connection string .
FAL_SERVER should be present in the standby machine’s tnsnames.ora so that the standby will know which primary database to communicate when there is a log gap and it sends the FAL_CLIENT value with it’s request. The primary will use the FAL_CLIENT to know to which standby database it should be sending the missing archive log files to.
For simplicity, it is best to have both the tnsnames.ora file of primary and standby to have the same values.
It would be a good idea to also have these settings in the primary database so that if a switchover happens the new standby will also benefit from the FAL processes.In our example the init.ora of the orioles instance(primary) on patty does not have it but it is adviced to have it over there too.

####################################
#Standby Parameters
####################################
FAL_SERVER='orioles_primary'
FAL_CLIENT='orioles_standby'
# standby_archive_dest is not compulsory as the default will be log_archive_dest_1 if it is absent.
standby_archive_dest='LOCATION=/mnt/xserve-raid/u02/oradata/orioles/arch'
#log_archive_dest_2='SERVICE=ltstdby OPTIONAL'
standby_file_management=auto #Allow rename, add, drop a data file
#remote_archive_enable=TRUE # deprecated

LOG_ARCHIVE_DEST_STATE_1=ENABLE
#LOG_ARCHIVE_DEST_STATE_2=ENABLE

#log_parallelism=1 # obsolete in 10g

###########################################
# ARchive logging
###########################################
 log_archive_dest_1='LOCATION=/mnt/xserve-raid/u04/oradata/orioles/arch'
 log_archive_format='%t_%s_%r.arc'
 #log_archive_start=FALSE # deprecated in 10g
 log_archive_max_processes=9

db_recovery_file_dest=''
dg_broker_config_file1=''
dg_broker_config_file2=''

5) tnsnames.ora file on both primary and standby

ORIOLES_PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = patty.wgenhq.net)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orioles.world)
    )
  )

ORIOLES_STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = selma.wgenhq.net)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orioles.world)
    )
  )

rmancat =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = marge.wgenhq.net)(PORT = 1527))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rmancat.world)
    )
)


 6) Please do the following at Patty to cleanup all the rman backup data as we want to start afresh

RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9453    Full    6.30M      DISK        00:00:00     25-JUN-07     
        BP Key: 9454   Status: AVAILABLE  Compressed: NO  Tag: TAG20070625T141518
        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1
  Control File Included: Ckp SCN: 13558656688   Ckp time: 25-JUN-07

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9497    4.86M      DISK        00:00:01     25-JUN-07     
………….
………….
………….
(Heavily edited )
  1    196     13558677195 25-JUN-07 13558678022 25-JUN-07

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9767    32.00K     DISK        00:00:01     25-JUN-07     
        BP Key: 9773   Status: AVAILABLE  Compressed: NO  Tag: TAG20070625T145043
        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1

  List of Archived Logs in backup set 9767
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    197     13558678022 25-JUN-07 13558678344 25-JUN-07
  1    198     13558678344 25-JUN-07 13558678367 25-JUN-07

RMAN>


Now we know that all backups are at : /mnt/xserve-raid2/u05/bkp

Now will delete all backups from the OS

patty:oracle>rm /mnt/xserve-raid2/u05/bkp/*


Connect back to rman and make the status as EXPIRED

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=190 devtype=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1 recid=33 stamp=626192119
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_13il5rqn_1_1 recid=34 stamp=626192216
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_14il5ru2_1_1 recid=35 stamp=626192323
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_15il5s2p_1_1 recid=36 stamp=626192473
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_16il5s38_1_1 recid=37 stamp=626192489
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_17il5s3b_1_1 recid=38 stamp=626192492
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_18il5sug_1_1 recid=39 stamp=626193360
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_19il5sun_1_1 recid=40 stamp=626193367
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_1ail5sup_1_1 recid=41 stamp=626193369
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1bil5sur_1_1 recid=42 stamp=626193372
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1 recid=43 stamp=626194245
Crosschecked 11 objects

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
9454    9453    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1
9499    9497    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_13il5rqn_1_1
9594    9554    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_14il5ru2_1_1
9633    9616    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_15il5s2p_1_1
9634    9617    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_16il5s38_1_1
9652    9650    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_17il5s3b_1_1
9708    9705    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_18il5sug_1_1
9709    9706    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_19il5sun_1_1
9721    9719    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_1ail5sup_1_1
9737    9735    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1bil5sur_1_1
9773    9767    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1 recid=33 stamp=626192119
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_13il5rqn_1_1 recid=34 stamp=626192216
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_14il5ru2_1_1 recid=35 stamp=626192323
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_15il5s2p_1_1 recid=36 stamp=626192473
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_16il5s38_1_1 recid=37 stamp=626192489
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_17il5s3b_1_1 recid=38 stamp=626192492
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_18il5sug_1_1 recid=39 stamp=626193360
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_19il5sun_1_1 recid=40 stamp=626193367
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_1ail5sup_1_1 recid=41 stamp=626193369
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1bil5sur_1_1 recid=42 stamp=626193372
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1 recid=43 stamp=626194245
Deleted 11 EXPIRED objects

RMAN> list backup;


RMAN>


Now the above list backup shows no records.

7) Run the following commands at patty to backup the primary database

RMAN> run {
2>  allocate channel d1 type disk maxpiecesize=10g;
3>  backup database format '/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_%d_%T_%U';
4>  backup current controlfile for standby format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_%d_%T_%U';
5>  #backup current controlfile  format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_%d_%T_%U';
6>  backup archivelog all format '/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_%d_%T_%U' delete input;
7>  release channel d1;
8>  }

released channel: ORA_DISK_1
allocated channel: d1
channel d1: sid=190 devtype=DISK

Starting backup at 28-JUN-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=/mnt/xserve-raid/u02/oradata/orioles/system01.dbf
input datafile fno=00002 name=/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf
input datafile fno=00004 name=/mnt/xserve-raid/u02/oradata/orioles/users01.dbf
input datafile fno=00003 name=/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf
channel d1: starting piece 1 at 28-JUN-07
channel d1: finished piece 1 at 28-JUN-07
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 tag=TAG20070628T111218 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 28-JUN-07
channel d1: finished piece 1 at 28-JUN-07
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1 tag=TAG20070628T111218 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JUN-07

Starting backup at 28-JUN-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including standby control file in backupset
channel d1: starting piece 1 at 28-JUN-07
channel d1: finished piece 1 at 28-JUN-07
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 tag=TAG20070628T111227 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JUN-07

Starting backup at 28-JUN-07
current log archived
channel d1: starting archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=199 recid=366 stamp=626392805
input archive log thread=1 sequence=200 recid=367 stamp=626439608
input archive log thread=1 sequence=201 recid=368 stamp=626440349
channel d1: starting piece 1 at 28-JUN-07
channel d1: finished piece 1 at 28-JUN-07
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 tag=TAG20070628T111230 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:04
channel d1: deleting archive log(s)
archive log filename=/mnt/xserve-raid/u03/oradata/orioles/arch/1_199_624645845.arc recid=366 stamp=626392805
archive log filename=/mnt/xserve-raid/u03/oradata/orioles/arch/1_200_624645845.arc recid=367 stamp=626439608
archive log filename=/mnt/xserve-raid/u03/oradata/orioles/arch/1_201_624645845.arc recid=368 stamp=626440349
Finished backup at 28-JUN-07

released channel: d1

RMAN>


8) scp all files to Selma from Patty to the backup location /mnt/xserve-raid2/u05/bkp

At Selma :

selma:oracle>hostname
selma.wgenhq.net
selma:oracle>pwd
/mnt/xserve-raid2/u05/bkp
selma:oracle>scp oracle@patty:/mnt/xserve-raid2/u05/bkp/* .
PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1    100%  335MB  55.9MB/s   00:06   
PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1                                                        100% 6464KB   6.3MB/s   00:00   
PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1                                                         100%  125MB  41.8MB/s   00:03   
PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1                                             100% 6464KB   6.3MB/s   00:00   
selma:oracle>

9)Create the standby database on Selma
At Selma:

Very Important: Please make sure the password file is created on the standby and the passwords are the same between primary and standby. Also set the relevant init.ora remote_login_passwordfile  is set to exclusive.

If password file is not available the primary will not be able to contact the standby to ship archive logs.

selma:oracle>export ORACLE_SID=orioles
selma:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 11:26:57 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  759169024 bytes
Fixed Size                  2075784 bytes
Variable Size             331350904 bytes
Database Buffers          419430400 bytes
Redo Buffers                6311936 bytes
SQL>

Run the rman duplicate command

rman target system/oracle@orioles_primary catalog rman_owner/rman_owner@rmancat auxiliary /
Recovery Manager complete.
wner@rmancat auxiliary / system/oracle@orioles_primary catalog rman_owner/rman_o

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 11:32:43 2007

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

connected to target database: ORIOLES (DBID=1460895637)
connected to recovery catalog database
connected to auxiliary database: ORIOLES (not mounted)

RMAN>

RMAN>  DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

Starting Duplicate Db at 28-JUN-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=211 devtype=DISK

contents of Memory Script:
{
   set until scn  13558802368;
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 28-JUN-07
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 tag=TAG20070628T111227
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/mnt/xserve-raid/u02/oradata/orioles/control01.ctl
output filename=/mnt/xserve-raid/u03/oradata/orioles/control02.ctl
Finished restore at 28-JUN-07

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
   set until scn  13558802368;
   set newname for tempfile  1 to
 "/mnt/xserve-raid/u03/oradata/orioles/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/mnt/xserve-raid/u02/oradata/orioles/system01.dbf";
   set newname for datafile  2 to
 "/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf";
   set newname for datafile  3 to
 "/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf";
   set newname for datafile  4 to
 "/mnt/xserve-raid/u02/oradata/orioles/users01.dbf";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /mnt/xserve-raid/u03/oradata/orioles/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-JUN-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=214 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /mnt/xserve-raid/u02/oradata/orioles/system01.dbf
restoring datafile 00002 to /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf
restoring datafile 00003 to /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf
restoring datafile 00004 to /mnt/xserve-raid/u02/oradata/orioles/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 tag=TAG20070628T111218
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 28-JUN-07

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=626441695 filename=/mnt/xserve-raid/u02/oradata/orioles/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=626441695 filename=/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=626441695 filename=/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=626441695 filename=/mnt/xserve-raid/u02/oradata/orioles/users01.dbf

contents of Memory Script:
{
   set until scn  13558802368;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 28-JUN-07
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=201
channel ORA_AUX_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 tag=TAG20070628T111230
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=/mnt/xserve-raid/u04/oradata/orioles/arch/1_201_624645845.arc thread=1 sequence=201
channel clone_default: deleting archive log(s)
archive log filename=/mnt/xserve-raid/u04/oradata/orioles/arch/1_201_624645845.arc recid=1 stamp=626441698
media recovery complete, elapsed time: 00:00:03
Finished recover at 28-JUN-07
Finished Duplicate Db at 28-JUN-07

RMAN>







10) Put the standby in managed recover mode

At Selma :

selma:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 11:36:53 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;
Connected.
SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  759169024 bytes
Fixed Size                  2075784 bytes
Variable Size             331350904 bytes
Database Buffers          419430400 bytes
Redo Buffers                6311936 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Check the alert log of standby database with log switches at the primary database

tail –f alert*

Thu Jun 28 11:45:53 2007
RFS[1]: No standby redo logfiles created
Thu Jun 28 11:45:53 2007
RFS[2]: No standby redo logfiles created
Thu Jun 28 11:45:53 2007
RFS[1]: Archived Log: '/mnt/xserve-raid/u02/oradata/orioles/arch/1_210_624645845.arc'
Thu Jun 28 11:45:53 2007
RFS[2]: Archived Log: '/mnt/xserve-raid/u02/oradata/orioles/arch/1_211_624645845.arc'
Thu Jun 28 11:45:55 2007
Media Recovery Log /mnt/xserve-raid/u02/oradata/orioles/arch/1_210_624645845.arc
Media Recovery Log /mnt/xserve-raid/u02/oradata/orioles/arch/1_211_624645845.arc
Media Recovery Waiting for thread 1 sequence 212










At primary orioles :

After multiple log switches

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /mnt/xserve-raid/u03/oradata/orioles/arch
Oldest online log sequence     210
Next log sequence to archive   212
Current log sequence           212


Now the standby is all done and we are setting ourselves up for bigger things like a full database recovery …..


FULL DATABASE RECOVERY


1.Cleanup all prior backup information

This can be done from both standby or primary. I am doing this from standby.

Physically deleting the files from both patty and Selma

patty:oracle>cd /mnt/xserve-raid2/u05/bkp
patty:oracle>ls
PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1
PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1
PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1
PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1
patty:oracle>rm *
patty:oracle>

selma:oracle>cd /mnt/xserve-raid2/u05/bkp
selma:oracle>ls
PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1              PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1
PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1  PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1
selma:oracle>rm *
selma:oracle>

At Selma :


RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=191 devtype=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 recid=44 stamp=626440338
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1 recid=45 stamp=626440346
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 recid=46 stamp=626440348
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 recid=47 stamp=626440351
Crosschecked 4 objects


RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
9805    9803    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1
9806    9804    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1
9820    9818    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1
9840    9838    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 recid=44 stamp=626440338
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1 recid=45 stamp=626440346
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 recid=46 stamp=626440348
deleted backup piece
backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 recid=47 stamp=626440351
Deleted 4 EXPIRED objects


RMAN> list backup;


RMAN>


2) Take the controlfile backup from patty (primary) and scp the file to Selma

patty:oracle>rman target / catalog rman_owner/rman_owner@rmancat

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 12:01:56 2007

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

connected to target database: ORIOLES (DBID=1460895637)
connected to recovery catalog database

RMAN> run {
2>  allocate channel d1 type disk maxpiecesize=10g;
3>  #backup database format '/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_%d_%T_%U';
4>  #backup current controlfile for standby format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_%d_%T_%U';
5>  backup current controlfile  format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_%d_%T_%U';
6>  #backup archivelog all format '/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_%d_%T_%U' delete input;
7>  release channel d1;
8>  }

starting full resync of recovery catalog
full resync complete
allocated channel: d1
channel d1: sid=214 devtype=DISK

Starting backup at 28-JUN-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 28-JUN-07
channel d1: finished piece 1 at 28-JUN-07
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1 tag=TAG20070628T120304 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JUN-07

released channel: d1

RMAN>

3) scp the controlfile back to Selma ( or might as well keep it there as it is anyways going to be needed at patty during recovery) when we blow out orioles at patty. I prefer to keep it there as this saves me some labour.


4) Perform the full database backup and archivelog backup at Selma

run {
 allocate channel d1 type disk maxpiecesize=10g;
 backup database  format '/mnt/xserve-raid2/u05/bkp/STANDBY_FULL_%d_%T_%U';
 backup archivelog all format '/mnt/xserve-raid2/u05/bkp/STANDBY_arc_%d_%T_%U' delete input;
 release channel d1;
 }

….
….
….

There were some syntax errors and therefore I had to fire the backup database and backup archivelog separately.
Anyways at the end , here is what I had


RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9981    Full    6.30M      DISK        00:00:00     28-JUN-07     
        BP Key: 9982   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120304
        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1
  Control File Included: Ckp SCN: 13558804034   Ckp time: 28-JUN-07


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10031   512.50K    DISK        00:00:01     28-JUN-07     
        BP Key: 10032   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120714
        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1

  List of Archived Logs in backup set 10031
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    202     13558802368 28-JUN-07 13558803405 28-JUN-07
  1    203     13558803405 28-JUN-07 13558803411 28-JUN-07
  1    204     13558803411 28-JUN-07 13558803611 28-JUN-07
  1    205     13558803611 28-JUN-07 13558803617 28-JUN-07
  1    206     13558803617 28-JUN-07 13558803622 28-JUN-07
  1    207     13558803622 28-JUN-07 13558803627 28-JUN-07
  1    208     13558803627 28-JUN-07 13558803630 28-JUN-07
  1    209     13558803630 28-JUN-07 13558803632 28-JUN-07
  1    210     13558803632 28-JUN-07 13558803635 28-JUN-07
  1    211     13558803635 28-JUN-07 13558803637 28-JUN-07

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10074   Full    336.34M    DISK        00:00:05     28-JUN-07     
        BP Key: 10077   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755
        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1
  List of Datafiles in backup set 10074
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/system01.dbf
  2       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf
  3       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf
  4       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10075   Full    6.30M      DISK        00:00:00     28-JUN-07     
        BP Key: 10078   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755
        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1
  Standby Control File Included: Ckp SCN: 13558803637   Ckp time: 28-JUN-07

RMAN>

selma:oracle>cd /mnt/xserve-raid2/u05/bkp/
selma:oracle>ls
STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1  STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1  STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1
selma:oracle>ls -l
total 351768
-rw-r-----  1 oracle oracle    525312 Jun 28 12:07 STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1
-rw-r-----  1 oracle oracle 352681984 Jun 28 12:07 STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1
-rw-r-----  1 oracle oracle   6619136 Jun 28 12:08 STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1
selma:oracle>pwd
/mnt/xserve-raid2/u05/bkp
selma:oracle>ls
STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1  STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1  STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1
selma:oracle>scp * oracle@patty:/mnt/xserve-raid2/u05/bkp/.
STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1                                                         100%  513KB 513.0KB/s   00:00   
STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1                                                        100%  336MB  56.1MB/s   00:06   
STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1                                                        100% 6464KB   6.3MB/s   00:00   
selma:oracle>







5)Blowup orioles on patty (Primary) or rather move it to a backup location

At Patty:

patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:19:38 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;
Connected.
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/mnt/xserve-raid/u02/oradata/orioles/system01.dbf
/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf
/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf
/mnt/xserve-raid/u02/oradata/orioles/users01.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/mnt/xserve-raid/u02/oradata/orioles/control01.ctl
/mnt/xserve-raid/u03/oradata/orioles/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/mnt/xserve-raid/u03/oradata/orioles/redo01a.log
/mnt/xserve-raid/u02/oradata/orioles/redo01b.log
/mnt/xserve-raid/u03/oradata/orioles/redo02a.log
/mnt/xserve-raid/u02/oradata/orioles/redo2b.log
/mnt/xserve-raid/u03/oradata/orioles/redo03a.log
/mnt/xserve-raid/u02/oradata/orioles/redo03b.log

6 rows selected.

patty:oracle>cd /mnt/xserve-raid/u02/oradata/orioles
patty:oracle>ls
control01.ctl  redo01b.log  redo03b.log  redo2b.log  system01.dbf  undo_tbs01.dbf  users01.dbf  users01.dbf.06222007
patty:oracle>mkdir bkp
patty:oracle>cp * ./bkp/.
cp: omitting directory `bkp'
patty:oracle>cd /mnt/xserve-raid/u03/oradata/orioles
patty:oracle>mkdir bkp
patty:oracle>cp * ./bkp/.
cp: omitting directory `arch'
cp: omitting directory `bkp'
patty:oracle>




6) Start the rman recovery at primary

At Patty:

patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:25:18 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  759169024 bytes
Fixed Size                  2075784 bytes
Variable Size             331350904 bytes
Database Buffers          419430400 bytes
Redo Buffers                6311936 bytes


patty:oracle>rman target / catalog rman_owner/rman_owner@rmancat

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 12:26:23 2007

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

connected to target database: orioles (not mounted)
connected to recovery catalog database

RMAN> list backup of controlfile;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9981    Full    6.30M      DISK        00:00:00     28-JUN-07     
        BP Key: 9982   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120304
        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1
  Control File Included: Ckp SCN: 13558804034   Ckp time: 28-JUN-07

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10075   Full    6.30M      DISK        00:00:00     28-JUN-07     
        BP Key: 10078   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755
        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1
  Standby Control File Included: Ckp SCN: 13558803637   Ckp time: 28-JUN-07


RMAN> run
2> { set until scn 13558804035; #one higher than above
3> restore controlfile;
4> }

executing command: SET until clause

Starting restore at 28-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1 tag=TAG20070628T120304
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/mnt/xserve-raid/u02/oradata/orioles/control01.ctl
output filename=/mnt/xserve-raid/u03/oradata/orioles/control02.ctl
Finished restore at 28-JUN-07


RMAN> run
2> {restore database; #no need to set time or scn as we want the latest
3> }

Starting restore at 28-JUN-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /mnt/xserve-raid/u02/oradata/orioles/system01.dbf
restoring datafile 00002 to /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf
restoring datafile 00003 to /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf
restoring datafile 00004 to /mnt/xserve-raid/u02/oradata/orioles/users01.dbf
channel ORA_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1 tag=TAG20070628T120755
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 28-JUN-07


patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:38:31 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;
Connected.
SQL> shutdown immediate
ORA-01507: database not mounted


sORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  759169024 bytes
Fixed Size                  2075784 bytes
Variable Size             331350904 bytes
Database Buffers          419430400 bytes
Redo Buffers                6311936 bytes
Database mounted.
SQL> exit


patty:oracle>rman target / catalog rman_owner/rman_owner@rmancat

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 12:39:23 2007

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

connected to target database: ORIOLES (DBID=1460895637, not open)
connected to recovery catalog database

RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9981    Full    6.30M      DISK        00:00:00     28-JUN-07     
        BP Key: 9982   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120304
        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1
  Control File Included: Ckp SCN: 13558804034   Ckp time: 28-JUN-07

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10031   512.50K    DISK        00:00:01     28-JUN-07     
        BP Key: 10032   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120714
        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1

  List of Archived Logs in backup set 10031
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    202     13558802368 28-JUN-07 13558803405 28-JUN-07
  1    203     13558803405 28-JUN-07 13558803411 28-JUN-07
  1    204     13558803411 28-JUN-07 13558803611 28-JUN-07
  1    205     13558803611 28-JUN-07 13558803617 28-JUN-07
  1    206     13558803617 28-JUN-07 13558803622 28-JUN-07
  1    207     13558803622 28-JUN-07 13558803627 28-JUN-07
  1    208     13558803627 28-JUN-07 13558803630 28-JUN-07
  1    209     13558803630 28-JUN-07 13558803632 28-JUN-07
  1    210     13558803632 28-JUN-07 13558803635 28-JUN-07
  1    211     13558803635 28-JUN-07 13558803637 28-JUN-07

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10074   Full    336.34M    DISK        00:00:05     28-JUN-07     
        BP Key: 10077   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755
        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1
  List of Datafiles in backup set 10074
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/system01.dbf
  2       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf
  3       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf
  4       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10075   Full    6.30M      DISK        00:00:00     28-JUN-07     
        BP Key: 10078   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755
        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1
  Standby Control File Included: Ckp SCN: 13558803637   Ckp time: 28-JUN-07

#if you look above the restored datafile backup scn was 13558803637 and but the archivelog backup was till
#13558803635 only. Luckily we have archive log sequence 212 on disk as we did not delete it and we know that this should cover-up
#recovery till 13558803637 .In case the archivelog availability scn was lower than this , we would have been forced to restore a earlier  #datafile backup. This is the maximum we can recover to as everything else like the online redo log on the primary is wiped out and we #depend entirely on the archive logs which have been backed up. So I use a SCN one higher than
#13558803637 which is 13558803638.This ensures recovery is done till 13558803637.

RMAN> run        
2> {
3> set until scn=13558803638;
4> recover database;
5> }

executing command: SET until clause

Starting recover at 28-JUN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

starting media recovery

archive log thread 1 sequence 212 is already on disk as file /mnt/xserve-raid/u02/oradata/orioles/redo2b.log
archive log filename=/mnt/xserve-raid/u02/oradata/orioles/redo2b.log thread=1 sequence=212
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-JUN-07

RMAN> run
2> {alter database open resetlogs;
3> }

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Let’s do some verification

patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:46:18 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;
Connected.
SQL> select name from v$database;

NAME
---------
ORIOLES

SQL> select * from recover_file;
select * from recover_file
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> desc v$recover_file
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE#                                              NUMBER
 ONLINE                                             VARCHAR2(7)
 ONLINE_STATUS                                      VARCHAR2(7)
 ERROR                                              VARCHAR2(18)
 CHANGE#                                            NUMBER
 TIME                                               DATE

SQL> select * from v$recover_file;

no rows selected

SQL> select username from dba_users;

USERNAME
------------------------------
TSMSYS
ROBERT
OUTLN
DIP
DBSNMP
SCOTT
GAUTHAM
SYS
SYSTEM

9 rows selected.

SQL>
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
UNDO_TBS                       ONLINE
USERS                          ONLINE



The database looks all good






















Posted by at No comments:
Subscribe to: Posts (Atom)