2nd project
imap.poe.doc.oracle@hp.com
W-HPI-INCLV4-IMIT-ORACLE
W-HPI-INCLV4-IMIT-SQL
W-HPI-INCLV4-IMIT-ARCHIVING
=====================================================================================================
UNIX
W-INCFLS-HPIT-UX
W-INCLV3-DC-CIS-UNIX
W-INCLV3-HPIT-BLUES-INFRASTRUCTURE
=====================================================================================================
UNIX L1/L2/L3:
W-INCLV3-DC-CIS-UNIX [L3 Support]
W-INCFLS-HPIT-RHYTHM-INFRASTRUCTURE[L2 Support]
W-INCLV3-HPIT-BLUES-INFRASTRUCTURE [L1 Support]
LINUX L1/L2/L3:
W-INCLV3-DC-CIS-LINUX [L3 Support]
W-INCLV3-HPIT-BLUES-INFRASTRUCTURE [L1& L2 Support]
BACKUP L1/L2/L3:
[L1 support]
W-INCLV3-DC-CIS-BACKUP [L3 Support]
STORAGE L1/L2/L3:
W-INCLV3-DC-CIS-STOR [L3 Support]
SAP Basis team AG :
W-INCFLS-HPIT-SAP-BASIS
=====================================================================================================
BACKUP
W-INCFLS-HPIT-BACKUP (L1)
W-INCLV3-DC-CIS-BACKUP (L3)
=====================================================================================================
SAN
W-INCLV3-DC-CIS-STOR
pdl = itibcsstoragedd@hp.com
ITIO AH Storage Demand and Delivery <itibcsstoragedd@hpe.com>
=====================================================================================================
HPE ORACLE
W-INCLV3-DC-DB-ORACLE
gdba.doc.oracle@hpe.com
=====================================================================================================
SAP BASIS
W-INCFLS-ESAPS-GSCS-AMOS-BASIS
W-INCFLS-HPIT-SAP-BASIS
=====================================================================================================
shared docs
\\10.20.13.17\HP_PAaS\HP_PAaS_RW \Team\Oracle
Outlook mail
https://intmail.hpicorp.net/owa/
HP Inc database Healthcheck
https://c1t16972.itcs.hpicorp.net/HC/ ---- what is it
https://c1t16972.itcs.hpicorp.net/HC/MC/BOECPP-GCU12804/index.html
DBA MyRoom
https://www.myroom.hp.com/attend/MRH4LVK3YEIUG
https://join.ucrtc.hp.com/meet/brian.pogson/B1TDZ7T3
IRC MyRoom
https://www.myroom.hp.com/attend/MRPYNUVQ6HQ3P
+1 8442929325 ---> HP My IT --> Password reset/unlock.
SRPA link
https://virtualsm.austin.hpicorp.net/cgi-bin/SrpaPrivAccess.pl?Pass=2&os=1&org=1&group=1
directory link
https://g4t8220.houston.hp.com/protected/
ACCESS LINE
http://accessline.com/login.asp
8772961750
5187777
OEM
https://oemprohpi.austin.hpicorp.net:1159/em
https://oemnonprohpi.austin.hpicorp.net/em/
http://g4u1698c.houston.hp.com:7788/em/console/logon/logon;jsessionid=gBTxWycTn6D23nGGpdNBX1122Nh0X9T2LR9jDJwZqr1VpbcGRkdt!-2092312585
http://g4u1698c.houston.hp.com:7788/em/console/logon/logon;jsessionid=gBTxWycTn6D23nGGpdNBX1122Nh0X9T2LR9jDJwZqr1VpbcGRkdt!-2092312585
https://oemprohpi.austin.hpicorp.net:1159/em/faces/core-uifwk-console-overview?_afrLoop=18859963269471064&_afrWindowMode=0&_afrWindowId=10zbq6q3cq_1#!%40%40%3F_afrWindowMode%3D0%26_afrWindowId%3D10zbq6q3cq_1%26_afrLoop%3D18859963269471064%26_adf.ctrl-state%3D10zbq6q3cq_9
HPSM
http://itsm-hpit.corp.hp.com/sm/index.do
APATE
https://apate.corp.hp.com/
Backup Report Link
http://mastertool.itcs.hp.com/NewCellServerLookup.aspx
http://backupvalidation.itcs.hp.com/backupvalidation.aspx
Share-point
https://hp.sharepoint.com/teams/hpit_backup/_layouts/15/start.aspx#/Lists/Database%20Backup%20Requests%20and%20Issues%20HPI/Not%20Complete.aspx
https://hp.sharepoint.com/teams/hpit_backup/_layouts/15/start.aspx#/Lists/Database%20Backup%20Requests%20and%20Issues%20HPI/AllItems.aspx - backup config request
DBA WAR Lync Room
https://databases.corp.hp.com/dap/ - Password reset
GDBA Account Provision <gdba.account.provision@hpe.com>;
we need to communicate with the tools team if there is any issue with the tool
Pathangi, Jayashree (GIT Global Delivery US GDBA) <jayashree.venkipuram-pathangi@hpe.com>
Hi Team,
Please use DAP link https://databases.corp.hp.com/dap/ to reset the password / to unlock from user end.
If any issues with the tool send an email to PDL gdba.account.provision@hpe.com along with the error message or screen shot.
For any DAP tool issue, reach out to mohd-fitri.jamaludin@hpe.com during IST hours.
And in PST reach out to jayashree.venkipuram-pathangi@hpe.com
GDBA will not reset the password to old password as per the security policy
http://databases.corp.hp.com/pulsecheck - password expiry date can be viewed without any ticket to GDBA
outage
http://harriben7.auth.hpicorp.net:8080/
Handover Link
https://hp-my.sharepoint.com/personal/brian_pogson_hp_com/_layouts/15/WopiFrame.aspx?sourcedoc={704BCE1D-33EE-44B2-8D6A-BE8661716C48}&file=PoeOps&action=default&d=w704bce1d33ee44b28d6abe8661716c48&RootFolder=%2fpersonal%2fbrian_pogson_hp_com%2fDocuments%2fPOE%20Operations%2fPoeOps
Documents:
http://hpi24.sharepoint.hp.com/teams/GDBAProcessDocumentation/partner/Pages/11gR2%20Client%20Configuration.aspxhttp://hpi24.sharepoint.hp.com/teams/GDBAProcessDocumentation/partner/Pages/11gR2%20Client%20Configuration.aspxhttp
Share point User access link
http://one.sharepoint.hp.com/teams/infra-unix-core-delivery/BCS_Wiki/Pages/NGDC%20HP-UX%20and%20Linux%20Account%20Management.aspx
http://propel-pro.houston.hp.com/propel/CMP_HPInc
Business Justification : Administer Oracle Database on HPUX 11.23 server which does not support LDAP access.
DB health check
https://databases.corp.hp.com/pulsecheck/default.aspx
======================================================================================================
http://www.dbas-oracle.com/2013/05/5-Easy-Step-to-Solve-ORA-04031-with-Oracle-Support-Provided-Tool.html
http://www.dbajunior.com/manage-grid-infrastructure/
http://propel-pro.houston.hp.com/propel/CMP_HPInc
http://www.dbajunior.com/manage-grid-infrastructure/
http://g4u1698c.houston.hp.com:7788/em/console/home - Hpq/ HPE OEM
georgejo
Welcome_2016
047 --> Token number ---> Shanmugham
W24018742
240187<OTP>
======================================================================================================
• For Oracle client, Add the below entry to sqlnet.ora file
TCP.CONNECT_TIMEOUT=1
• For JDBC connection, Add below code to the connection properties file
Properties prop = new Properties ();
prop.put (oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR,"" + (1 * 1000)); // 1 second dbPools[ poolIndex ].setConnectionProperties ( prop );
Company: HPI or HPE
Requestor: E-Mail of the MI requester
Application Name / EPR ID: This is needed to ensure we are opening against the correct application
Criticality: MC/EE /NL
Type: Down / Partial Down / Poor Performance / Data Timeliness
Problem Statement: What is broken ?
Business Impact: What are we not able to do because of this issue ?
IM number: Ticket used to open the MI
============================================================================================================
To create the tkt for unix team
W-INCFLS-HPIT-UX--- unix grp
for CI select the server name and service as the DB
=====================================================================================
To check status of SRPA request :
https://virtualsm.austin.hpicorp.net/cgi-bin/SrpaManager.pl
==========================================
our queue name in HPSM.
w-hpi-chgimp-imit-data-services
=================================
OEM link :
OEM
https://oemprohpi.austin.hpicorp.net:1159/em
https://oemnonprohpi.austin.hpicorp.net/em/
===================================================
HP room.
MRPH7VCVMM9KN --- HP room.
MRHVZKXDV36R3 Holiday period
===================================================
https://c1w23054.itcs.hpicorp.net/ON/Default.aspx -- Oracle outage notification . This needs to be filled up whenever there is a MI or even any database outage or even one instance is down for critical databases.
==========
IM25889902
S2S_B1_bddbpdb_BDDBP_log_del g1u3308c.austin.hpicorp.net "BDDBP" error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified
RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified
Recovery Manager complete.
[Major] From: ob2rman@g1u3308c.austin.hpicorp.net "BDDBP" Time: 02/03/18 12:42:43
External utility reported error.
RMAN PID=24808
[Major] From: ob2rman@g1u3308c.austin.hpicorp.net "BDDBP" Time: 02/03/18 12:42:43
The database reported error while performing requested operation.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04004: error from recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified
Recovery Manager complete.
[Major] From: ob2rman@g1u3308c.austin.hpicorp.net "BDDBP" Time: 02/03/18 12:42:43
Oracle Recovery Manager completed with errors.
TO check spec:
--------------
/opt/omni/bin/omnirpt -report dl_info -tab | grep TRSTGP
To check last 15 days backup status
-------------------------------------------
/opt/omni/bin/omnidb -ses -data "Oracle8 S2S_B1_bddbpdb_BDDBP_log_del" -last 15
To check backup Log
-------------------------
/opt/omni/bin/omnidb –ses 2017/07/11-792 -report
/opt/omni/bin/omnidb –details 2017/07/06-683 -report
omnidb -ses 2018/02/04-531 -report
To run backup manually
-----------------------------
nohup /opt/omni/bin/omnib -oracle8_list S2S_B1_bddbpdb_BDDBP_log_del -protect days 15 > /tmp/S2S_B1_bddbpdb_BDDBP_log_del.log &
rman target omnibackup/omni_PDMLI@PDMLI catalog RMAN_PDMLI/dcc_rman@AUSRCII
rman target omnibackup/omni_MA9@MA9 catalog rman_MA9/dcc_rman@AUSRCIP
rman target omnibackup/omni_D7C@D7C catalog RMAN_D7C/dcc_rman@AUSRC3P
/opt/omni/lbin/util_cmd -getmethod Oracle8 1016865727 -- >(DBID- --select dbid from v$database;
rman target omnibackup/omni_BDDBP@BDDBP catalog rman_BDDBP/dcc_rman@AUSRCIP
sqlplus omnibackup/omni_BDDBP@BDDBP
rman catalog rman_BDDBP/dcc_rman@AUSRCIP
sqlplus rman_BDDBP/dcc_rman@AUSRCIP
g1u3309c:home/oracle $ /opt/omni/lbin/util_cmd -getmethod Oracle8 1016865727
ORACLE_VERSION='11.2.0';
CLIENT=('bddbpdb.glb1.hpicorp.net%BDDBP');
DB_NAME='BDDBP';
TGT_NLS_LANG='AMERICAN_AMERICA.AL32UTF8';
CATALOG={
CATALOG_DB_VERSION='11.1.0.7.0';
RCPasswd='FHBBEHBBEHBBQGBBDIBBOHBBCHBBPHBB';
RC_NLS_LANG='AMERICAN_AMERICA.AL32UTF8';
RCService='AUSRC1P';
RCUser='rman_BDDBP';
HOST_NAME='';
};
*RETVAL*0
g1u3309c:home/oracle $
February 2018Kumar, Vijay (IS DBA Services)
00:33
./validateD.ksh -C AUSRCIP -D BDDBP -P omni_AUSRCIP
g1u3309c:oracle/stage $ ps -ef | grep pmon
oracle 27767 1 0 Jan 30 ? 6:49 ora_pmon_BDDBP2
oracle 7606 1016 0 19:05:06 pts/0 0:00 grep pmon
g1u3309c:oracle/stage $
g1u3714g:home/oracle $ find / -name validateD.ksh 2>/dev/null
/u01/app/oracle/stage/work/validateD.ksh
/u01/app/oracle/stage/validateD.ksh
./validateD.ksh -C AUSRCII -D ECLMAPI -P omni_AUSRCII
/opt/omni/lbin/util_oracle8.pl -config -dbname BDDBP -orahome /u01/app/oracle/product/11.2.0/db_2 -prmuser omnibackup -prmpasswd omni_BDDBP -prmservice BDDBP -rcuser rman_BDDBP -rcpasswd dcc_rman -rcservice AUSRCIP -client bddbpdb.glb1.hpicorp.net
g1u2560
g1u3309c:oracle/stage $ ./validateD.ksh -C AUSRCIP -D BDDBP -P omni_AUSRCIP
======================================================================================
Starting Backup Validation for Database : BDDBP on catalog AUSRCIP V 5.0 Apr 2015
======================================================================================
Last successful login: Sun Feb 4 18:59:23 UTC 2018
Database connection OK!
Database BDDBP in ARCHIVELOG mode !
Database BDDBP is OPEN !
Database BDDBP configuration OK !
User account RMAN_BDDBP status is not LOCKED or EXPIRED !
User RMAN_BDDBP exists !
Verified user RMAN_BDDBP able to connect to AUSRCIP.
Select permission on V$_INSTANCE available for user RMAN_BDDBP !
RESOURCE Role granted to user RMAN_BDDBP !
RECOVERY_CATALOG_OWNER role granted to RMAN_BDDBP !
CREATE SESSION privilege granted to RMAN_BDDBP !
SNAPSHOT controlfile location in shared location !
------>> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/admin/BDDBP/backups/snapcf_BDDBP.f';
Checking link to tnsnames.ora in ORACLE_HOME of database BDDBP
/u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora correctly linked to /u01/app/oracle/admin/tns_admin/tnsnames.ora !
------>> /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora -> /u01/app/oracle/admin/tns_admin/tnsnames.ora
Last successful login: Sun Feb 4 19:06:18 UTC 2018
Site Controller package bddbp_sc found !
Checking Site controller package state bddbp_sc !
Last successful login: Sun Feb 4 19:09:54 UTC 2018
Site controller package bddbp_sc is not enabled, expecting it to be enabled. Check FAILED !
Last successful login: Sun Feb 4 19:09:55 UTC 2018
Site controller IP configured - 15.70.66.194 ( bddbp-1151-sc-b1.austin.hpicorp.net )
Site controller IP configured - 15.94.11.154 ( bddbp-1151-sc-b2.austin.hpicorp.net )
Database backup configuration for BDDBP validated OK !
======================================================================================
============
g1u3309c:home/oracle $ /opt/omni/lbin/util_oracle8.pl -config -dbname BDDBP -orahome /u01/app/oracle/product/11.2.0/db_2 -prmuser omnibackup -prmpasswd omni_BDDBP -prmservice BDDBP -rcuser rman_BDDBP -rcpasswd dcc_rman -rcservice AUSRCIP -client bddbpdb.glb1.hpicorp.net
*RETVAL*0
g1u3309c:home/oracle $ g1u3309c:home/oracle $
g1u3309c:home/oracle $
g1u3309c:home/oracle $
g1u3309c:home/oracle $ /opt/omni/lbin/util_cmd -getmethod Oracle8 1016865727
DB_NAME='BDDBP';
CLIENT=('bddbpdb.glb1.hpicorp.net%BDDBP');
ORACLE_VERSION='11.2.0';
TGT_NLS_LANG='AMERICAN_AMERICA.AL32UTF8';
CATALOG={
CATALOG_DB_VERSION='11.2.0.3.0';
RCPasswd='FHBBEHBBEHBBQGBBDIBBOHBBCHBBPHBB';
RC_NLS_LANG='AMERICAN_AMERICA.AL32UTF8';
RCService='AUSRCIP';
RCUser='rman_BDDBP';
HOST_NAME='';
};
*RETVAL*0
==============================================================
g1u3309c:oracle/stage $ cat validateD.ksh
#!/usr/bin/ksh
export user=omnibackup
#export catuser=RMAN_${db}
export catpwd=dcc_rman
export catsysuser=omnibackup
export catsyspwd=omni_$cat
export cnt=0
export cnt2=0
export success=1
export connfail=0
export ver="5.0 Apr 2015"
function usage
{
echo "usage: ./validateD.ksh -C <CATALOGNAME> -d <DBNAME> -P <CATALOG OMNIBACKUP PASSWORD>"
echo " "
echo " -c / -C --> catalog name"
echo " -d / -D --> Database name "
echo " -P / -p --> Omnibackup password if it's different from omni_CATALOGNAME"
echo " -h --> help"
echo " "
echo " "
echo " e.g."
echo " "
echo " 1. Validate backup configuration for database CRNAI with catalog as HOU3RCI"
echo " "
echo " ./validateD.ksh -C HOU3RCI -D CRNAI "
echo " "
echo " 2. Validate backup configuration for database CRNAP with catalog as HOU3RCP"
echo " If the validation is being run from site 2 in S2S configuration the catalog "
echo " is aliased same as primary site but with different service name. So, provide"
echo " omnibackup password for actual catalog used on site "
echo " "
echo " ./validateD.ksh -C HOU3RCP -D CRNAP -P omni_HOU9RCP"
exit
}
function doCheck
{
echo "select sysdate from dual;" > dual.sql
echo "exit" >>dual.sql
echo " " >>dual.sql
echo " "
echo "======================================================================================"
echo "Starting Backup Validation for Database : ${db} on catalog ${cat} V ${ver} "
echo "======================================================================================"
if [ -n "$upwd" ]
then
export catpwd=$upwd
fi
set +u
#inst=`cat /etc/oratab | grep -v "#" | cut -f1 -d: | grep -w ${db} `
inst=`ps -ef|grep ${db} | grep pmon | awk '{ print $NF }' | cut -f3 -d_`
if [ -z "$inst" ]
then
echo "Entry for database ${db} not found in /etc/oratab ! Exiting. "
exit 1
fi
ISSG=0
ISS2SMC=0
ISSG=`ps -ef|egrep "cmclconfd|cmserviced|cmcluster|cmlvmd" | wc -l`
if [ $ISSG -gt 2 ]
then
ISS2SMC=`pbrun cmviewcl -l node | egrep -v "SITE_NAME|NODE" | awk '{ print substr($1,1,2) }' | sort | grep -v "^$" | uniq | wc -l`
fi
if [ -d /oracle/${db} ]
then
SAP=1
else
SAP=0
fi
unset ORAENV_ASK
set +u
echo $inst | . /usr/local/bin/oraenv >/dev/null 2>&1
$ORACLE_HOME/bin/sqlplus -S -L ${user}/${pwd}@${db} @dual.sql >conn.log 2>&1
connmsg=`grep ORA- conn.log`
grep ORA-28002 conn.log >/dev/null 2>&1
if [ $? -eq 0 ]
then
$ORACLE_HOME/bin/sqlplus -S -L / as sysdba<<!
create profile test limit password_life_time unlimited password_reuse_time unlimited password_reuse_max unlimited password_verify_function null;
col password new_value PASSWORD
col profile new_value OLDPROFILE
col username new_value USER
select username from dba_users where username = 'OMNIBACKUP';
select password from user$ where name = '&&USER';
select profile from dba_users where username = '&&USER';
alter user &&USER profile test;
alter user &&USER identified by values '&&PASSWORD';
alter user &&USER profile &&OLDPROFILE;
drop profile test;
exit;
!
fi
$ORACLE_HOME/bin/sqlplus -S -L ${user}/${pwd}@${db} @dual.sql >conn.log 2>&1
connmsg=`grep ORA- conn.log`
grep -i ora- conn.log >/dev/null 2>&1
if [ $? -eq 0 ]
then
echo "Unable to connect to database ${db} using ${user}/${pwd} !"
echo "MSG : $connmsg "
connfail=1
exit 1
else
echo "Database connection OK! "
cnt=$(($cnt+1))
$ORACLE_HOME/bin/sqlplus -S -L ${catsysuser}/${catsyspwd}@${cat} as sysdba @dual >/dev/null 2>&1
if [ $? -ne 0 ]
then
echo "Unable to login to catalog ${cat} using standard omnibackup/${catsyspwd} !"
exit 1;
fi
$ORACLE_HOME/bin/sqlplus -S -L ${user}/${pwd}@${db} as sysdba @dual.sql > privchk.out 2>&1
grep "ORA-01031" privchk.out >/dev/null 2>&1
if [ $? -eq 0 ]
then
echo "OMNIBACKUP does not seem to have SYSDBA privilege !"
fi
$ORACLE_HOME/bin/sqlplus -S ${user}/${pwd}@${db} as sysdba <<! > privchk.out 2>&1
set pages 0 head off feedback off sqlprompt ''
select name,open_mode,log_mode from gv\$database;
select * from gv\$pwfile_users where username='OMNIBACKUP';
exit;
!
arcmode=`grep -w "ARCHIVELOG" privchk.out`
openmode=`grep -w "READ WRITE" privchk.out`
if [[ -n "$arcmode" ]] && [[ $connfail -ne 1 ]]
then
echo "Database ${db} in ARCHIVELOG mode !"
cnt=$(($cnt+1))
else
echo "Database ${db} not in ARCHIVELOG mode - FAILED !";
fi
if [[ -n "$openmode" ]] && [[ $connfail -ne 1 ]]
then
echo "Database ${db} is OPEN !"
cnt=$(($cnt+1))
else
echo "Database ${db} is not OPEN !"
fi
if [ $cnt -gt 2 ]
then
echo "Database ${db} configuration OK !"
fi
fi
$ORACLE_HOME/bin/sqlplus -S -L ${catsysuser}/${catsyspwd}@${cat} as sysdba @dual.sql >/dev/null 2>&1
if [ $? -eq 0 ]
then
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<! >privs.out
set pages 0 head off feedback off sqlprompt ''
select username from dba_users where username = '${catuser}';
select account_status from dba_users where username = '${catuser}';
select privilege from dba_tab_privs where grantee = '${catuser}';
select granted_role from dba_role_privs where grantee = '${catuser}';
select privilege from dba_sys_privs where grantee = '${catuser}';
exit;
!
if [ -s privs.out ]
then
break;
fi
else
continue
fi
user=`grep ${catuser} privs.out`
vinst=`grep SELECT privs.out`
resource=`grep RESOURCE privs.out`
recovery=`grep RECOVERY_CATALOG_OWNER privs.out`
session=`grep SESSION privs.out`
locked=`grep OPEN privs.out`
if [ -n "$locked" ]
then
echo "User account ${catuser} status is not LOCKED or EXPIRED !"
else
echo "User account ${catuser} either LOCKED or EXPIRED, please check !"
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<! >> rman.out
alter user ${catuser} account unlock;
exit;
!
fi
if [ -n "$user" ]
then
echo "User ${catuser} exists !"
$ORACLE_HOME/bin/sqlplus -S -L ${catuser}/${catpwd}@${cat} @dual.sql >rmanconn.log 2>&1
grep "ORA-01017" rmanconn.log >/dev/null 2>&1
if [ $? -eq 0 ]
then
echo "RMAN user ${catuser} not using standard password dcc_rman, resetting password !"
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<! >> rman3.out
create profile testprof limit password_life_time unlimited password_reuse_time unlimited password_reuse_max unlimited password_verify_function null;
alter user ${catuser} profile testprof;
alter user ${catuser} identified by dcc_rman;
alter user ${catuser} profile HP_GDBA_SERVICE;
drop profile testprof;
exit;
!
fi
$ORACLE_HOME/bin/rman target omnibackup/${pwd}@${db} catalog ${catuser}/${catpwd}@${cat} <<! > rman.out
resync catalog;
show all;
exit;
!
grep "RCVCAT database is not current" rman.out >/dev/null 2>&1
if [ $? -eq 0 ]
then
echo "RMAN catalog version mismatched database binary version - check FAILED !"
cnt2=$(($cnt2-1))
fi
$ORACLE_HOME/bin/sqlplus -S -L ${catuser}/${catpwd}@${cat} @dual.sql >rmanconn.log 2>&1
if [ $? -eq 0 ]
then
echo "Verified user $catuser able to connect to $cat."
dbreg=`grep RMAN-20001 rman.out`
if [ -n "$dbreg" ]
then
echo "Target database not registered in catalog ! Registering Now !!!!"
$ORACLE_HOME/bin/rman target omnibackup/${pwd}@${db} catalog ${catuser}/${catpwd}@${cat} <<! >/dev/null 2>&1
register database;
resync catalog;
exit;
!
cnt2=$(($cnt2+1))
fi
else
echo "User ${catuser} exist but unable to connect using standard username and password !"
fi
else
echo "User ${catuser} does not exist in RMAN catalog ${cat} !"
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<!
create profile RMAN_USER_CREATION limit PASSWORD_LIFE_TIME UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED;
create user RMAN_${db} identified by dcc_rman default tablespace CATALOG temporary tablespace TEMP quota unlimited on CATALOG PROFILE RMAN_USER_CREATION;
grant recovery_catalog_owner to RMAN_${db};
grant create session to RMAN_${db};
grant resource to RMAN_${db};
grant select on v_\$instance to RMAN_${db};
alter user RMAN_${db} profile HP_GDBA_SERVICE_NOEXPIRY;
drop profile RMAN_USER_CREATION;
exit;
!
sftp -o "BatchMode=yes" g2w1172.austin.hp.com <<!
bye
!
if [ $? -eq 0 ]
then
sftp g2w1172.austin.hp.com <<!
cd /orahpux/autoinstall/scripts/
get db_initial_setup.cmd
bye
!
else
ftp -n g2w1172.austin.hp.com<<!
user anonymous
a
cd /orahpux/autoinstall/scripts/
get db_initial_setup.cmd
bye
!
fi
$ORACLE_HOME/bin/rman target omnibackup/${pwd}@${db} catalog ${catuser}/${catpwd}@${cat} <<!
@db_initial_setup.cmd
exit;
!
fi
if [ -n "$vinst" ]
then
echo "Select permission on V\$_INSTANCE available for user ${catuser} !"
cnt2=$(($cnt2+1))
else
echo "Missing SELECT permission on V\$_INSTANCE ! "
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<!
grant select on v_\$instance to ${catuser};
exit;
!
fi
if [ -n "$resource" ]
then
echo "RESOURCE Role granted to user ${catuser} !"
cnt2=$(($cnt2+1))
else
echo "Missing RESOURCE role !"
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<!
grant RESOURCE to ${catuser};
exit;
!
fi
if [ -n "$recovery" ]
then
echo "RECOVERY_CATALOG_OWNER role granted to ${catuser} !"
cnt2=$(($cnt2+1))
else
echo "Missing RECOVERY_CATALOG_OWNER role !"
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<!
grant RECOVERY_CATALOG_OWNER to ${catuser};
exit;
!
fi
if [ -n "$session" ]
then
echo "CREATE SESSION privilege granted to ${catuser} !"
cnt2=$(($cnt2+1))
else
echo "Missing CREATE SESSION privilege !"
$ORACLE_HOME/bin/sqlplus -S ${catsysuser}/${catsyspwd}@${cat} as sysdba <<!
grant create session to ${catuser};
exit;
!
fi
if [ $SAP -eq 0 ]
then
snapcf=`grep "CONFIGURE SNAPSHOT CONTROLFILE" rman.out | awk '{ print $6 }'`
grep "CONFIGURE SNAPSHOT CONTROLFILE" rman.out | grep "/u01/app/oracle/admin" > /dev/null 2>&1
if [ $? -eq 0 ]
then
echo "SNAPSHOT controlfile location in shared location !"
echo " ------>> `grep \"CONFIGURE SNAPSHOT CONTROLFILE\" rman.out `"
cnt2=$(($cnt2+1))
else
echo "SNAPSHOT controlfile location verification failed - ${snapcf} !"
echo "Attempting to fix!"
if [ -d /u01/app/oracle/admin/${db} ]
then
$ORACLE_HOME/bin/rman target omnibackup/${pwd}@${db} catalog ${catuser}/${catpwd}@${cat} <<! >/dev/null 2>&1
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/admin/${db}/backups/snapcf_${db}.f';
exit;
!
$ORACLE_HOME/bin/rman target omnibackup/${pwd}@${db} catalog ${catuser}/${catpwd}@${cat} <<! >rman2.out 2>&1
show all;
exit;
!
grep "CONFIGURE SNAPSHOT CONTROLFILE" rman2.out | grep "/u01/app/oracle/admin"
if [ $? -eq 0 ]
then
echo "SNAPSHOT controlfile location fixed !"
cnt2=$(($cnt2+1))
else
cnt2=$(($cnt2-1))
fi
fi
fi
else
cnt2=$(($cnt2+1)) # just to increment counter
fi
if [ $SAP -eq 0 ]
then
echo "Checking link to tnsnames.ora in ORACLE_HOME of database ${db}"
OHOME=`cat /etc/oratab | grep -v "#" | grep ${db} | cut -f2 -d:`
if [ -h $OHOME/network/admin/tnsnames.ora ]
then
echo "${OHOME}/network/admin/tnsnames.ora correctly linked to $TNS_ADMIN/tnsnames.ora !"
echo " ------>> `ls -al $OHOME/network/admin/tnsnames.ora | awk '{ print substr($0,index($0,$9)) }'`"
else
if [ -f $OHOME/network/admin/tnsnames.ora ]
then
echo "tnsnames.ora existing in $OHOME/network/admin, but not linked to $TNS_ADMIN !"
#mv ${OHOME}/network/admin/tnsnames.ora ${OHOME}/network/admin/tnsnames.ora.backup
#ln -s $TNS_ADMIN/tnsnames.ora $OHOME/network/admin/tnsnames.ora
else
echo "Unable to find link to $TNS_ADMIN/tnsnames.ora in $OHOME/network/admin, please fix tnsnames.ora link! "
#ln -s $TNS_ADMIN/tnsnames.ora $OHOME/network/admin/tnsnames.ora
#if [ $? -eq 0 ]
#then
# echo "$TNS_ADMIN/tnsnames.ora linked to $OHOME/network/admin/tnsnames.ora"
#else
# echo "Error linking $TNS_ADMIN/tnsnames.ora to $OHOME/network/admin/tnsnames.ora, backup config will fail with error ORA-12154
# !"
#fi
fi
fi
fi
cat /etc/oratab | grep -v "#" | grep ${db} | cut -f1 -d: | . oraenv > /dev/null 2>&1
if [ -d /u01/app/grid -a `ps -ef | grep grid | egrep -v "egrep|grep|vxpal" | wc -l` -ne 0 ]
then
GRID=`ls -al /u01/app/grid | grep 112 | tail -1 | awk '{ print $NF }'`
GRID_HOME=/u01/app/grid/${GRID}
GRIDVERS=`$GRID_HOME/bin/crsctl query crs activeversion | awk '{ print $NF }'`
$ORACLE_HOME/OPatch/opatch lsinv | grep 10317487 > /dev/null 2>&1
if [[ $? -ne 0 ]] && [[ "$GRIDVERS" = "[11.2.0.2.0]" ]]
then
echo "ODM Patch for snapshot controlfile 10317487 missing. Please patch or upgrade to 11.2.0.3 !"
cnt2=$(($cnt2-1))
fi
fi
#set -x
if [ $ISS2SMC -gt 1 ]
then
if [ $SAP -eq 1 ]
then
SC=`pbrun cmviewcl | grep dbci | grep -i ${db} | awk '{ print $1 }'`
else
SC=`pbrun cmviewcl | grep sc| grep -i ${db} | awk '{ print $1 }'`
fi
if [ -n "$SC" ]
then
echo "Site Controller package ${SC} found !"
echo "Checking Site controller package state ${SC} !"
SCSTAT=`pbrun cmviewcl |grep ${SC} | grep -v "PACKAGE" | awk '{ print $4 }'`
if [ "$SCSTAT" != "enabled" ]
then
echo "Site controller package ${SC} is not enabled, expecting it to be enabled. Check FAILED !"
cnt2=$(($cnt2-1))
else
echo "Site controller package ${SC} is enabled - SUCCESSFUL."
cnt2=$(($cnt2+1))
fi
SGSTYLE=`pbrun cmviewcl -p ${SC} -v | grep -i Style | awk '{ print $2 }'`
if [ "$SGSTYLE" = "modular" ]
then
cmgetconf -p ${SC} >/dev/null 2>&1
if [ $? -eq 0 ]
then
cmgetconf -p ${SC} -v | grep -v "#" | sort | grep -v "^$" > ${SC}.config 2>&1
else
pbrun cmgetconf -p ${SC} -v | grep -v "#" | sort | grep -v "^$" > ${SC}.config 2>&1
fi
SCCFG="${SC}.config"
else
if [ $SAP -eq 1 ]
then
if [ -f /etc/cmcluster/${db}/${SC}.control.script ]
then
SCCFG="/etc/cmcluster/${db}/${SC}.control.script"
else
SCCFG="/etc/cmcluster/${SC}/${SC}.control.script"
fi
else
SCCFG="/etc/cmcluster/${SC}/${SC}.config"
fi
fi
if [ -f $SCCFG ]
then
SCIP=`egrep "^ip_address|IP\[" ${SCCFG} | egrep -v "#|\@" | sed -e 's/=/ /g' -e 's/\"//g' | awk '{ print $2 }'`
if [ -n "$SCIP" ]
then
sccnt=0
for IP in $SCIP
do
SCNAME=`nslookup $IP | grep Name | awk '{ print $2 }'| egrep "dbci|-sc-"`
if [ -n "$SCNAME" ]
then
echo "Site controller IP configured - ${IP} ( ${SCNAME} ) "
#cnt2=$(($cnt2+1))
sccnt=$(($sccnt+1))
#else
#echo "Site controller IP not configured, check FAILED ! "
#cnt2=$(($cnt2-1))
# sccnt=$(($sccnt-1))
fi
done
if [ $sccnt -gt 1 ]
then
cnt2=$(($cnt2+2))
else
echo "Site controller IP not configured, check FAILED !"
cnt2=$(($cnt2-2))
fi
else
echo "Site controller IP not configured, check FAILED !"
cnt2=$(($cnt2-1))
fi
else
echo "Unable to read site controller config file - ${SCCFG}, check FAILED !"
cnt2=$(($cnt2-1))
fi
else
echo "Site controller not configured, check FAILED ! "
cnt2=$(($cnt2-1))
fi
fi
#set +x
if [ $SAP -eq 1 ]
then
if [ $ISS2SMC -gt 1 ]
then
comp=6
else
comp=4
fi
else
comp=4
fi
if [[ $cnt -gt 2 ]] && [[ $cnt2 -gt $comp ]]
then
echo "Database backup configuration for ${db} validated OK !"
else
echo "Database backup configuration validation for ${db} FAILED !"
fi
echo "======================================================================================"
echo " "
}
if [ $# -lt 1 ]
then
usage
exit 1
fi
while [ "$1" != "" ]; do
case $1 in
-c | -C ) shift
if [ "$1" != "" ]
then
cat=$1
else
echo "Missing catalog name !"
usage
exit 1;
fi
;;
-d | -D ) shift
if [ "$1" != "" ]
then
db=$1
pwd=omni_${db}
catuser=`echo RMAN_${db} | tr '[a-z]' '[A-Z]'`
else
echo "Missing database name !"
usage
exit 1;
fi
;;
-P | -p ) shift
if [ "$1" != "" ]
then
catsyspwd=$1
else
echo "Missing catalog omnibackup password !"
usage
exit 1;
fi
;;
-h | -H ) usage
exit
;;
* ) usage
exit 1
esac
shift
done
doCheck
g1u3309c:oracle/stage $
=====================================================
IM26043932 = sql Tuning.
====================================
oraapc> cat Applied_log_on_standby_delete.ksh
#!/bin/ksh
#
# Script Name: DeleteAppliedArchLogs
# Created: Iurii Rimbu, 02.08.2004
# based on the SQL script from Martin Lehmann
#
# This script will delete applied archived logs
# on the physical standby database
#
# UPDATES:
# 09-10-07, PNM, Add . oraenv and send mail alfter deleting logs.
# 09/29/05, CVE, Add "-d" option for database name.
# 04/14/05, CVE, Modifications for SDBU.
#
#CVE# Added following variable so we can use script on non-standby db:
#CVE# If you want to make sure the db is in standby mode, then
#CVE# STANDBY_REQUIRED=1
STANDBY_REQUIRED=1
PROGNAME=`basename $0`
QUOTE="'"
# "HPO-2" messages are informative messages, not monitored by ITO
MESSAGE_CODE="HPO-20000"
USAGE="usage ${PROGNAME}: |-h|-d <Database Name> [-k <hours to keep>]\n"
USAGE="${USAGE}\t -h Display Help\n"
USAGE="${USAGE}\t -k <how many hours to keep applied archivelogs, default 1 hour>\n"
USAGE="${USAGE}\t -d <Database Name, or SID>\n"
USAGE="${USAGE}Example: ${PROGNAME} -d ESRVS -k 4 \n"
CheckInstance()
{
echo "Checking that the instance is up and running on this node"
#CVE# ? $HTMLLOG is not used anywhere else ?
ps -edf |grep -i "ora_pmon_${ORACLE_SID}\$" > /dev/null 2>&1
ret_code=$?
if [ ${ret_code} != 0 ] ; then
echo "No instance running on this node !!"
echo "Exiting..."
exit 0
fi
}
CheckStandbyInstance() {
echo "Check if this database instance is the primary one"
PRIMARY_HERE=`sqlplus -s "/as sysdba" <<!
set head off
set trimout on
set pagesize 1000
select count(*) from v\\$instance where INSTANCE_ROLE=${QUOTE}PRIMARY_INSTANCE${QUOTE};
exit
!`
if [ ${PRIMARY_HERE} -eq 1 ]
then
echo "Primary instance is here"
echo "Running the script further ..."
else
echo "Primary instance is not here or sqlplus error"
exit 0
fi
if [ ${STANDBY_REQUIRED} = 1 ] ; then
ps -ef | grep ${ORACLE_SID} | grep ora_mrp0 >/dev/null 2>&1
ret_code=$?
if [ ${ret_code} != 0 ] ; then
echo "Warning: mrp0 process not running, i.e. physical standby is not in recovery mode"
echo "Exit 0"
exit 0
else
echo "mrp0 process is running, i.e. physical standby is in recovery mode"
echo "Running the script further ..."
fi
DB_ROLE=`sqlplus -s "/as sysdba" <<!
set head off
set trimout on
set pagesize 1000
select count(*) from v\\$database where DATABASE_ROLE=${QUOTE}PHYSICAL STANDBY${QUOTE};
exit
!`
if [ ${DB_ROLE} -eq 1 ]
then
echo "Database role is PHYSICAL STANDBY"
echo "Running the script further ..."
else
echo "Database role is not PHYSICAL STANDBY or sqlplus error"
exit 0
fi
fi
} #End CheckStandbyInstance
DeleteAppliedArchLogs() {
ALERT_LOG=`sqlplus -s "/ as sysdba" <<!
set heading off feedback off verify off pagesize 0
select value||${QUOTE}/alert_${ORACLE_SID}.log${QUOTE}
from v\\$parameter
where name=${QUOTE}background_dump_dest${QUOTE};
exit;
!`
echo
echo "START deleting applied archive logs ( keep time - ${KEEP_HOURS} hour(s) )..."
echo
#CVE# decision inserted for non-standby db:
if [ ${STANDBY_REQUIRED} = 1 ] ; then
APPLIED_LIST=`sqlplus -s "/ as sysdba" <<!
set heading off feedback off verify off pagesize 0
SELECT name
from v\\$ARCHIVED_LOG
where APPLIED='YES'
and completion_time > sysdate - 10
and completion_time < sysdate - ${KEEP_HOURS}/24
order by name;
exit;
!`
else
APPLIED_LIST=`sqlplus -s "/ as sysdba" <<!
set heading off feedback off verify off pagesize 0
SELECT name
from v\\$ARCHIVED_LOG
where completion_time < sysdate - ${KEEP_HOURS}/24
order by name;
exit;
!`
fi
for i in $APPLIED_LIST
do
if [ -f ${i} ] ; then
rm -rf $i
if [ $? = 0 ] ; then
MESSAGE="Applied archived log ${i} was deleted"
echo $MESSAGE >> $LOGDIR/deleted_logs.${ORACLE_SID}
if [ -w "$ALERT_LOG" ]; then
echo "`date`" >> ${ALERT_LOG} >> $LOGDIR/deleted_logs.${ORACLE_SID}
echo "${MESSAGE_CODE} Info: ${MESSAGE}" >> ${ALERT_LOG} >> $LOGDIR/deleted_logs.${ORACLE_SID}
else
echo "`date`" >> $LOGDIR/deleted_logs.${ORACLE_SID}
echo "${MESSAGE_CODE} Info: ${MESSAGE}" >> $LOGDIR/deleted_logs.${ORACLE_SID}
fi
else
fileowner=`/usr/bin/ls -l $i |awk '{print $3}'`
user=`/usr/bin/whoami`
if [ "$fileowner" != "$user" ]; then
logowner=`/usr/bin/ls -ld $LOGDIR |awk '{print $3}'`
if [ "$logowner" = "$user" ]; then
chmod 770 $LOGDIR
fi
echo "ssh -l $fileowner `hostname` \"/usr/bin/nohup $script -d $ORACLE_SID -k $KEEP_HOURS >/dev/null 2>&1 &\""
ssh -l $fileowner `hostname` "/usr/bin/nohup $script -d $ORACLE_SID -k $KEEP_HOURS >/dev/null 2>&1 &" > /dev/null 2>&1
exit
fi
echo "Failed to delete archived log ${i}" > $LOGDIR/failed_delete_logs.${ORACLE_SID}
exit 1
fi
fi
done
echo
echo "*** END deleting applied archive logs ( keep time - ${KEEP_HOURS} hour(s) ) *** " >> $LOGDIR/deleted_logs.${ORACLE_SID}
echo
mv $LOGDIR/deleted_logs.${ORACLE_SID} $LOGDIR/deleted_logs.${ORACLE_SID}_old
chmod 664 $LOGDIR/deleted_logs.${ORACLE_SID}_old
} #End CheckPrimaryInstance
####################################################
######## MAIN ####################
####################################################
#
# Check Command Line Arguments
#
export script="$0"
while getopts h:d:k: opt
do
case ${opt} in
h) printf "${USAGE}\n"
exit 0
;;
d) ORACLE_SID="${OPTARG}"
;;
k) KEEP_HOURS="${OPTARG}"
;;
*) printf "${USAGE}\n"
exit -1
;;
esac
done
shift `expr ${OPTIND} - 1`
#
# Validate input parameters
#
if [ -z "${KEEP_HOURS}" ] ; then
KEEP_HOURS=1
fi
if [ -d "/u01/app/oracle/admin/scripts/logs" ]; then
LOGDIR=/u01/app/oracle/admin/scripts/logs
elif [ -d "/u01/app/oracle/reorg/threshold/logs" ]; then
LOGDIR=/u01/app/oracle/reorg/threshold/logs
elif [ -d "/oracle/stage/threshold/logs" ]; then
LOGDIR=/oracle/stage/threshold/logs
else
LOGDIR=/tmp
fi
# Set the Oracle environment
# PNM Added so that it will not prompt for SID
export PATH=/usr/local/bin:$PATH
ORAENV_ASK=NO
. oraenv ${ORACLE_SID}
export PATH=$ORACLE_HOME/bin:$PATH
# Check if DB is running
export computer_name=`uname -n`
CheckInstance
# Check primary instance: the script will run only on the primary instance
CheckStandbyInstance
#Delete
DeleteAppliedArchLogs
exit 0
