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"
No comments:
Post a Comment