SQWareCentral:oracle

auto

sqwora_CheckInstance.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_CheckInstance.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_CheckInstance.ksh.
Check parameters and security

Action:

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_OraBin/../tools/sqwora_SetEnv.ksh $lvsqw_Instance
\$gvsqw_OraBin/sqwora_RunJob.ksh -I $lvsqw_Instance -A CheckInstance
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}

sqwora_DeplScripts.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_DeplScripts.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_DeplScripts.ksh.
SQWareProduction deployment for Oracle

Action:

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p $gvsqw_RootSQWareProductionOracle/$gvsqw_VersDeplSQWareProductionOracle
cd $gvsqw_RootSQWareProductionOracle
rm -f $gvsqw_VersSQWareProductionOracle
ln -s $gvsqw_VersDeplSQWareProductionOracle $gvsqw_VersSQWareProductionOracle
EOFSSH
rsync -aHv $gvsqw_RsyncPathOracle --delete $gvsqw_RootSQWareProduction/oracle/$gvsqw_VersDeplSQWareProductionOracle $lvsqw_UserName@$lvsqw_HostName:$gvsqw_RootSQWareProductionOracle/.
done <$gvsqw_TmpFile.lst
}

sqwora_GatherIndicators.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_GatherIndicators.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_GatherIndicators.ksh.
Oracle gather indicators

Action:

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_OraBin/../tools/sqwora_SetEnv.ksh $lvsqw_Instance
\$gvsqw_OraBin/sqwora_RunJob.ksh -I $lvsqw_Instance -A IndicDba
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}

sqwora_GenEdt.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_GenEdt.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_GenEdt.ksh.
generate EDT files

Action:

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
. \$gvsqw_OraBin/../tools/sqwora_SetEnv.ksh $lvsqw_Instance
\$gvsqw_OraBin/../tools/sqwora_GenerateCreateDatabase.ksh -P 1521 -edtOnly <<EOF
y
EOF
EOFSSH
done <$gvsqw_TmpFile.lst
}

sqwora_GenerateAwr.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_GenerateAwr.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_GenerateAwr.ksh.
collection of AWR reports

Action:

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_OraBin/../tools/sqwora_SetEnv.ksh $lvsqw_Instance
\$gvsqw_OraBin/sqwora_RunJob.ksh -I $lvsqw_Instance -A GenAwr
EOFSSH
sleep 10
done <$gvsqw_TmpFile.lst
jobs
wait
}

sqwora_RetrieveHash.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_RetrieveHash.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_RetrieveHash.ksh.
retrieve hashs for Oracle users

Action:

lfsqw_Action()
{
lvsqw_FicRef=$gvsqw_RootexpdbCentral/etc_cust/Hash_oracle.csv
> $lvsqw_FicRef
chmod 600 $lvsqw_FicRef
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH>$lvsqw_FicRef.tmp
. \$gvsqw_OraBin/../tools/sqwora_SetEnv.ksh $lvsqw_Instance
\$gvsqw_OraBin/../tools/sqwora_BackupPasswd.ksh -I $lvsqw_Instance -T hash
EOFSSH
cat $lvsqw_FicRef.tmp|grep "^$lvsqw_Instance;" >>$lvsqw_FicRef
done <$gvsqw_TmpFile.lst

}

sqwora_RmanFull.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_RmanFull.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_RmanFull.ksh.
Backup full with RMAN (incremental level 0)

Action:

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_OraBin/../tools/sqwora_SetEnv.ksh $lvsqw_Instance
\$gvsqw_OraBin/sqwora_RunJob.ksh -I $lvsqw_Instance -A RmanFull
EOFSSH
sleep 30
done <$gvsqw_TmpFile.lst
jobs
wait
}

sqwora_RmanInc.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_RmanInc.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_RmanInc.ksh.
Backup inc with RMAN (incremental level 2)

Action:

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_OraBin/../tools/sqwora_SetEnv.ksh $lvsqw_Instance
\$gvsqw_OraBin/sqwora_RunJob.ksh -I $lvsqw_Instance -A RmanInc
EOFSSH
sleep 30
done <$gvsqw_TmpFile.lst
jobs
wait
}

sqwora_StartedInstances.ksh

Action script $gvsqw_RootexpdbCentral/action/oracle/auto/sqwora_StartedInstances.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/auto_cust/sqwora_StartedInstances.ksh.
collection of active instances

Action:

lfsqw_Action()
{
>$gvsqw_TmpFile
while read lvsqw_HostName lvsqw_UserName
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH>>$gvsqw_TmpFile
ps -edf |grep 'pmon_[a-zA-Z][a-zA-Z]*'|grep -v grep|awk '{print "indic $lvsqw_HostName",\$1,\$NF}'|sed 's,ora_pmon_,,'
EOFSSH
done <$gvsqw_TmpFile.lst

grep '^indic ' $gvsqw_TmpFile >$gvsqw_TmpFile.bis
echo "delete tsqw_StartedInstances;">$gvsqw_TmpFile.sql
while read lvsqw_Tmp lvsqw_Host lvsqw_User lvsqw_Instance
do
	echo "insert into tsqw_StartedInstances (ORACLE_SID,GATHER_DATE,HOST_NAME,USERNAME) values ('$lvsqw_Instance',trunc(sysdate),'$lvsqw_Host','$lvsqw_User');">>$gvsqw_TmpFile.sql
done <$gvsqw_TmpFile.bis
echo "commit;" >>$gvsqw_TmpFile.sql
sqlplus -s -L /nolog <<EOFSQL|grep -iv 'Connected'
conn $gvsqw_DbRefUser_oracle/$gvsqw_DbRefPass_oracle@$gvsqw_DbRefInstance_oracle
@$gvsqw_TmpFile.sql
EOFSQL
}

etc

CrontabRef.cfg

Configuration file $gvsqw_RootexpdbCentral/action/oracle/etc/CrontabRef.cfg:
It contains the operating variables of SQWareCentral for oracle.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/oracle/etc_cust/CrontabRef.cfg
your modifications (only variables you want to modify).

File content:

Variable explanation:

sqwc_DbRefVar.cfg

Configuration file $gvsqw_RootexpdbCentral/action/oracle/etc/sqwc_DbRefVar.cfg:
It contains the operating variables of SQWareCentral for oracle.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/oracle/etc_cust/sqwc_DbRefVar.cfg
your modifications (only variables you want to modify).

File content:

export gvsqw_DbRefType_oracle='oracle'
export gvsqw_DbRefPort_oracle='InstancePortForOracle'
export gvsqw_DbRefInstance_oracle='InstanceNameForOracle'
export gvsqw_DbRefDatabase_oracle='DatabaseInstanceNameForOracle'
export gvsqw_DbRefUser_oracle='ConnexionUserForOracle'
export gvsqw_DbRefPass_oracle='ConnexionPassForOracle'

Variable explanation:

gvsqw_DbRefDatabase_oracle => 
gvsqw_DbRefInstance_oracle => 
gvsqw_DbRefPass_oracle => 
gvsqw_DbRefPort_oracle => 
gvsqw_DbRefType_oracle => 
gvsqw_DbRefUser_oracle => 

sqwc_ExpVar.cfg

Configuration file $gvsqw_RootexpdbCentral/action/oracle/etc/sqwc_ExpVar.cfg:
It contains the operating variables of SQWareCentral for oracle.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/oracle/etc_cust/sqwc_ExpVar.cfg
your modifications (only variables you want to modify).

File content:

export gvsqw_RootSQWareProductionOracle="~$gvsqw_UserUniqueOracle/SQWareProduction/oracle"
export gvsqw_VersSQWareProductionOracle='prod'
export gvsqw_VersDeplSQWareProductionOracle='v1.2'
export gvsqw_Mail_oracle='dba'
export gvsqw_MailMsg_oracle='TrtOraAuto'

Variable explanation:

gvsqw_MailMsg_oracle => begining of email subject for Oracle
gvsqw_Mail_oracle => email adress for Oracle
gvsqw_RootSQWareProductionOracle => SQWareProduction directory source for Oracle
gvsqw_VersDeplSQWareProductionOracle => version of SQWareProduction to deploy for Oracle
gvsqw_VersSQWareProductionOracle => link for current version of SQWareProduction for Oracle

sqwc_GlobalVar.cfg

Configuration file $gvsqw_RootexpdbCentral/action/oracle/etc/sqwc_GlobalVar.cfg:
It contains the operating variables of SQWareCentral for oracle.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/oracle/etc_cust/sqwc_GlobalVar.cfg
your modifications (only variables you want to modify).

File content:

export gvsqw_UserUniqueOracle='sysdba'
	export gvsqw_NbDaysInc=3
	export gvsqw_NbDaysInc=2
export gvsqw_NbDaysFull=7

Variable explanation:

gvsqw_NbDaysFull => 
	gvsqw_NbDaysInc => 
	gvsqw_NbDaysInc => 
gvsqw_UserUniqueOracle => 

tnsnames.ora

Configuration file $gvsqw_RootexpdbCentral/action/oracle/etc/tnsnames.ora:
It contains the operating variables of SQWareCentral for oracle.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/oracle/etc_cust/tnsnames.ora
your modifications (only variables you want to modify).

File content:

Variable explanation:

.profile_confort

File to put aliases $gvsqw_RootexpdbCentral/action/oracle/etc/.profile_confort:
File to put aliases $gvsqw_RootexpdbCentral/action/oracle/etc/.profile_confort:
Contains confort aliases of SQWareCentral for RDBMS oracle.
Contains confort aliases of SQWareCentral for RDBMS oracle.

Alias explanation:

act_ora => 'cd $gvsqw_RootexpdbCentral/action/oracle/prod;ls -al'
depl_ora => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh oracle sqwora_DeplScripts.ksh sqwora_GenLstUnique.ksh NOLOG CONFIRM'
repind_ora => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh oracle sqwora_GatherIndicators.ksh sqwora_GenLstRepind.ksh NOLOG'
gen_ora => '$gvsqw_RootexpdbCentral/action/oracle/lst/sqwc_GenRefOracle.ksh'

lst

sqwc_GenRefDbOracle.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwc_GenRefDbOracle.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwc_GenRefDbOracle.ksh.


Query used:

select 'ORA;' || vol.ORACLE_SID || ';' || vol.OWNER || ';' || ref.Env || ';' ||
from tsqw_Repository ref, tsqw_VolSchema vol, tsqw_GenHisto hist
where hist.MONTH=trunc(sysdate,'MM')
    and ref.ORACLE_SID = hist.ORACLE_SID
    and hist.ORACLE_SID = vol.ORACLE_SID
    and hist.GATHER_DATE = vol.GATHER_DATE
    and ref.STATUS!='OFF'
    and vol.OWNER not in ('SYSTEM','SYSMAN','OTLN')
    and vol.OWNER not like 'OPS$%'
order by vol.OWNER, vol.ORACLE_SID

sqwc_GenRefOracle.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwc_GenRefOracle.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwc_GenRefOracle.ksh.


Query used:

select virt_host_name vip_, host_name uni_, username usr_, oracle_sid bas_,
from tsqw_Repository r
select virt_host_name vip_, host_name uni_, username usr_, oracle_sid bas_,
from tsqw_RepositoryOther r
order by 2,1,3,4;

sqwora_GenLstAwr.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstAwr.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstAwr.ksh.
Generation of the list of Oracle instances >= 10g (tsqw_Repository/tsqw_GenHisto)

Query used:

select ref.VIRT_HOST_NAME||' '||ref.USERNAME||' '||ref.ORACLE_SID
from tsqw_Repository ref
where ref.STATUS = 'ON'
and exists ( select 1 from tsqw_GenHisto inf
where inf.GATHER_DATE = trunc(sysdate)
and ref.ORACLE_SID = inf.ORACLE_SID
and to_number(substr(inf.VERSION,0,instr(inf.VERSION,'.')-1)||substr(inf.VERSION,instr(inf.VERSION,'.')+1,1)) >= 102)
order by 1;

sqwora_GenLstInstance.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstInstance.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstInstance.ksh.
Generation of the list of Oracle instances (tsqw_Repository)

Query used:

select VIRT_HOST_NAME||' '||USERNAME||' '||ORACLE_SID
from tsqw_Repository
where STATUS = 'ON'
order by VIRT_HOST_NAME, USERNAME;

sqwora_GenLstInstanceNoProd.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstInstanceNoProd.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstInstanceNoProd.ksh.
Generation of the list of Oracle no production instances (tsqw_Repository)

Query used:

select VIRT_HOST_NAME||' '||USERNAME||' '||ORACLE_SID
from tsqw_Repository
where STATUS = 'ON'
and ENV != 'PRD'
order by VIRT_HOST_NAME, USERNAME;

sqwora_GenLstInstanceProd.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstInstanceProd.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstInstanceProd.ksh.
Generation of the list of Oracle production instances (tsqw_Repository)

Query used:

select VIRT_HOST_NAME||' '||USERNAME||' '||ORACLE_SID
from tsqw_Repository
where STATUS = 'ON'
and ENV = 'PRD'
order by VIRT_HOST_NAME, USERNAME;

sqwora_GenLstRepBckFull.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstRepBckFull.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstRepBckFull.ksh.
Generation of the list for reprise of full backups (tsqw_Repository/tsqw_VolBackups)

Query used:

select ref.VIRT_HOST_NAME||' '||ref.USERNAME||' '||ref.ORACLE_SID
from tsqw_Repository ref
where not exists ( select 1 from tsqw_VolBackups inf
                    where inf.BEGINNING >= sysdate-$gvsqw_NbDaysFull
                    and ref.ORACLE_SID = inf.ORACLE_SID
                    and inf.BCK_TYPE like '%incremental level 0%'
                    and inf.STATUS = 0
and not exists ( select 1 from tsqw_VolBackupsExclude ecl
                    where ref.ORACLE_SID = ecl.ORACLE_SID)
and ref.STATUS='ON'
order by 1;

sqwora_GenLstRepBckInc.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstRepBckInc.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstRepBckInc.ksh.
Generation of the list for reprise of inc backups (tsqw_Repository/tsqw_VolBackups)

Query used:

select ref.VIRT_HOST_NAME||' '||ref.USERNAME||' '||ref.ORACLE_SID
from tsqw_Repository ref
where not exists ( select 1 from tsqw_VolBackups inf
                    where inf.BEGINNING >= sysdate-$gvsqw_NbDaysInc
                    and ref.ORACLE_SID = inf.ORACLE_SID
                    and inf.BCK_TYPE like '%incremental level%'
                    and inf.STATUS = 0
and not exists ( select 1 from tsqw_VolBackupsExclude ecl
                    where ref.ORACLE_SID = ecl.ORACLE_SID)
and ref.STATUS='ON'
order by 1;

sqwora_GenLstRepind.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstRepind.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstRepind.ksh.
Generation of the list for reprise of indicators (tsqw_Repository/tsqw_GenHisto)

Query used:

select ref.VIRT_HOST_NAME||' '||ref.USERNAME||' '||ref.ORACLE_SID
from tsqw_Repository ref
where ref.STATUS = 'ON'
and not exists ( select 1 from tsqw_GenHisto inf
where inf.GATHER_DATE = trunc(sysdate)
and ref.ORACLE_SID = inf.ORACLE_SID)
order by VIRT_HOST_NAME, ref.USERNAME;

sqwora_GenLstUnique.ksh

List generator $gvsqw_RootexpdbCentral/action/oracle/lst/sqwora_GenLstUnique.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/oracle/lst_cust/sqwora_GenLstUnique.ksh.
Generation of the user unique list (tsqw_Repository/tsqw_RepositoryOther)

Query used:

select distinct HOST_NAME||' $gvsqw_UserUniqueOracle'
from tsqw_Repository
where STATUS = 'ON'
select distinct HOST_NAME||' $gvsqw_UserUniqueOracle'
from tsqw_RepositoryOther
order by 1;