#!/bin/ksh ############################################################################ # Script Name..: # Description..: This script will reset the APEX port in case of hang # Author.......: Michael Culp # Date.........: 10/11/2009 # Version......: 2.1 # Modified By..: # Date Modified: # Comments.....: # Schema owner.: # alter session set current # Login User...: # Run Order....: # Dependent on.: # Script type..: # ############################################################################ sqlplus -s "/ as sysdba" <<EOF exec dbms_xdb.sethttpport(0); exec dbms_xdb.sethttpport(8081); EOF
#!/bin/ksh # This script assigns the user that will be used in oracle to access tables in APEX # M. Culp # 11/06/2010 # 01/04/2010 Made changes to reflect the LS commands # 02/03/2010 # This should give access to the user in APEX that this is going against # connect dba01/password; # this incorporates the apl table from 106 ####################################################################################################### sqlplus -s "/ as sysdba" <<EOF spool 105_cr_dba01.log alter session set current_schema=DBA01; -- This is for the security drop table global_user_role; create table global_user_role (gur_user_id VARCHAR2(10), gur_usr_rl_nm VARCHAR2(10) ); -- Add at least one person in there to get in insert into global_user_role values('MCULP','ADMIN'); insert into global_user_role values('DVENUS','ADMIN'); drop table global_role; CREATE TABLE global_role ( gr_role_name VARCHAR2(10), gr_rl_dscr VARCHAR2(200) ) ; insert into global_role (gr_role_name,gr_rl_dscr) values('ADMIN','Master Admin'); insert into global_role (gr_role_name,gr_rl_dscr) values('MGMT','Management View'); insert into global_role (gr_role_name) values('DBA1'); insert into global_role (gr_role_name) values('DBA2'); insert into global_role (gr_role_name) values('DBA'); insert into global_role (gr_role_name) values('VIEW1'); insert into global_role (gr_role_name) values('VIEW2'); insert into global_role (gr_role_name) values('UNIX'); drop table cst_awr; CREATE TABLE CST_AWR ( CST_AWR_CST_ID VARCHAR2(10), CST_AWR_IMP_NO VARCHAR2(10), CST_AWR_DT DATE, CST_AWR_DB_ID NUMBER, CST_AWR_CMT VARCHAR2(4000), CST_AWR_SNP_STRT NUMBER, CST_AWR_SNP_END NUMBER ) ; drop table customer; CREATE TABLE CUSTOMER ( CST_ID NUMBER, CST_NM VARCHAR2(100), CST_TP NUMBER, CST_TEL_NBR VARCHAR2(15), CST_EML VARCHAR2(100), CST_CMT VARCHAR2(4000), CST_PRI_CNT VARCHAR2(75), CST_PRI_TEL VARCHAR2(15), CST_PRI_EML VARCHAR2(100), CST_LOB VARCHAR2(20), CST_DPT VARCHAR2(20), CST_BUS_OWR VARCHAR2(20), CST_PYMT_TP VARCHAR2(3) ) ; drop table database; CREATE TABLE DATABASE ( DB_ID VARCHAR2(10), DB_NM VARCHAR2(20), DB_DSCR VARCHAR2(1000), DB_CMT VARCHAR2(4000), DB_QADT DATE, DB_INST_NM VARCHAR2(15), DB_TP VARCHAR2(3), DB_VER VARCHAR2(3), DB_OS VARCHAR2(3), DB_OS_VER VARCHAR2(3), DB_UPG_VER VARCHAR2(3), DB_UPG_STAT VARCHAR2(3), DB_DBA_CRT VARCHAR2(3), DB_APL VARCHAR2(3), DB_DBA_CUR VARCHAR2(3), DB_CRT_DT DATE, DB_DT_NXT_UPG DATE, DB_DT_UPG DATE, DB_CMP VARCHAR2(3), DB_DBA_PRIM VARCHAR2(3), DB_DBA_SECD VARCHAR2(3), DB_CST_ID NUMBER, DB_CPU NUMBER, DB_DSK_USED NUMBER, DB_DSK_ALLOC NUMBER, -- these are really more instance level parameters DB_MEM_PGA NUMBER, DB_MEM_SGA NUMBER, DB_MEM_TGT NUMBER, -- this is the database DBID DB_DBID VARCHAR2(25), DB_BKP_FRQ VARCHAR2(25), DB_SVC_NM VARCHAR2(25), DB_CAT_TP VARCHAR2(25), DB_DBA_LU VARCHAR2(25), DB_DBA_NU VARCHAR2(25), DB_MCH_ID VARCHAR2(10) ); drop table dba; CREATE TABLE DBA ( DBA_ID NUMBER, DBA_FNAME VARCHAR2(40), DBA_LNAME VARCHAR2(40), DBA_TEL_01 VARCHAR2(20), DBA_TEL_02 VARCHAR2(20), DBA_EML VARCHAR2(100), DBA_DUKE_ID VARCHAR2(10), DBA_CMP_ID VARCHAR2(10) ); drop TABLE DB_TYPE ; CREATE TABLE DB_TYPE ( DB_TYP_ID VARCHAR2(3), DB_TYP_DSC VARCHAR2(100) ); drop TABLE HOSTINST; CREATE TABLE HOSTINST ( HI_ID VARCHAR2(4), HI_INST VARCHAR2(15), HI_DB_VER VARCHAR2(3), HI_RCVY_REQ VARCHAR2(3) ) ; drop table machine; CREATE TABLE MACHINE ( MCH_ID VARCHAR2(10), MCH_NM VARCHAR2(80), MCH_DSCR VARCHAR2(1000), MCH_IP VARCHAR2(15), MCH_CMT VARCHAR2(4000), MCH_STS VARCHAR2(4), MCH_HSTNME VARCHAR2(80), MCH_LOGIN VARCHAR2(40), MCH_PSWD VARCHAR2(40), MCH_UNAME VARCHAR2(40), MCH_OS_TP VARCHAR2(4), MCH_CMP VARCHAR2(3), MCH_RCVY_REQ VARCHAR2(3), MCH_OS_VER VARCHAR2(3), MCH_MEM_SZE VARCHAR2(4), MCH_MKE VARCHAR2(4), MCH_MDL VARCHAR2(3), MCH_PRC_NO VARCHAR2(3), MCH_UPTIME TIMESTAMP (6), MCH_DNS_NME VARCHAR2(40), MCH_LOB VARCHAR2(15), MCH_TP VARCHAR2(15), MCH_MEM_SZ number, MCH_MAX_MEM_SZE number, MCH_PRC_CNT number, MCH_VPRC_CNT number, MCH_FRM VARCHAR2(15), MCH_VIP VARCHAR2(15), MCH_IP_EN1 VARCHAR2(15), MCH_IP_SCN01 VARCHAR2(15), MCH_IP_SCN02 VARCHAR2(15), MCH_IP_SCN03 VARCHAR2(15), MCH_CLST_NM VARCHAR2(15), MCH_MNF VARCHAR2(15), MCH_PWD VARCHAR2(15), MCH_DNS_NM VARCHAR2(15) ) ; drop table saw; create table saw ( saw_id number, saw_lob varchar2(10), saw_clst_nm varchar2(20), saw_hstnm varchar2(20), saw_db_nm varchar2(20), saw_piccaso_nbr varchar2(15), saw_piccaso_dt date, saw_cust_rqst_dt date, saw_lpar_dlv_dt date, saw_gi_bld_dt date, saw_dbct_bld_dt date, saw_cpl_dt date, saw_prj_nm varchar2(20), saw_tot_cpu number, saw_new_srv_bld varchar2(1), saw_apvl_dt date, saw_sa varchar2(25), saw_cmt varchar2(4000), saw_srv_nm varchar2(25), saw_cge_mthd varchar2(25), saw_inst_cge_cpu varchar2(25), saw_tot_lpar_mem varchar2(25), saw_vrtl_ip varchar2(18), saw_pub_ip varchar2(18), saw_priv_ip varchar2(18), saw_scan_ip_1 varchar2(18), saw_scan_ip_2 varchar2(18), saw_scan_ip_3 varchar2(18), saw_sox_stat varchar2(18), saw_prd_stat varchar2(18), saw_tier varchar2(18), saw_perf_lvl varchar2(18), saw_pri_cntt_grp varchar2(18), saw_pri_cntt varchar2(18), saw_bus_own varchar2(18), saw_allcn_pl_nm varchar2(18), saw_bus_unit varchar2(18), saw_lcl_cntt varchar2(18), saw_aggr_dsk_iops number, saw_mem_rqst number, saw_aix_ver number, saw_lpar_dsk_chnl number, saw_lpar_dsk_chnl_typ varchar2(25), saw_lpar_dsk_chnl_spd number, saw_dba_mem_rqst number ) ; drop table cls; CREATE TABLE CLS ( CLS_ID NUMBER, CLS_NM VARCHAR2(20), CLS_SCN_NM VARCHAR2(20), CLS_SCN_IP01 VARCHAR2(20), CLS_SCN_IP02 VARCHAR2(20), CLS_SCN_IP03 VARCHAR2(20) ) ; drop table os; CREATE TABLE OS ( OS_ID NUMBER, OS_DSCR VARCHAR2(50), OS_VNDR VARCHAR2(100) ) ; drop table prj; CREATE TABLE PRJ ( PRJ_ID VARCHAR2(4), PRJ_BUS_FCN VARCHAR2(4), PRJ_STS VARCHAR2(15), PRJ_DBA VARCHAR2(4), PRJ_CMT VARCHAR2(4000), PRJ_CMTS VARCHAR2(4000), PRJ_STG VARCHAR2(10), PRJ_PRTY VARCHAR2(10), PRJ_DSCN VARCHAR2(255), PRJ_NM VARCHAR2(100), PRJ_LOB VARCHAR2(20), PRJ_TEAM VARCHAR2(10), PRJ_TP VARCHAR2(10), PRJ_LEAD VARCHAR2(10), PRJ_RDBMS VARCHAR2(10), PRJ_FTE_ALLCN VARCHAR2(10), PRJ_STRT_DT DATE, PRJ_CMPLT_DT DATE, PRJ_PRJTD_CMPLT_DT DATE ); drop table prj_mnt_tm; CREATE TABLE PRJ_MNT_TM ( PRJ_MNT_ID VARCHAR2(4), PRJ_MNT_DT DATE, PRJ_MNT_HOSTINST VARCHAR2(3), PRJ_MNT_TFROM TIMESTAMP (6), PRJ_MNT_TUNTIL TIMESTAMP (6) ) ; drop table equipment; create table equipment ( eqp_id varchar2(10), eqp_dscr varchar2(4), eqp_nm varchar2(25), eqp_tp varchar2(4), eqp_sn varchar2(50), eqp_lcn_id varchar2(50) ) ; drop table schema; CREATE TABLE SCHEMA ( SCHM_ID VARCHAR2(4), SCHM_DSCR VARCHAR2(200), SCHM_PWD VARCHAR2(40), SCHM_VSN VARCHAR2(10), SCHM_APP NUMBER, SCHM_CMP VARCHAR2(4), SCHM_USER VARCHAR2(50), SCHM_CRT_DT DATE, SCHM_MDF_DT DATE ) ; drop table apl; CREATE TABLE apl ( apl_ID VARCHAR2(4), apl_dscr VARCHAR2(200), apl_nm VARCHAR2(40), apl_cmp_id VARCHAR2(10), apl_inf_tier NUMBER, apl_rto VARCHAR2(4), apl_rpo VARCHAR2(4), apl_abbr VARCHAR2(50), apl_tier VARCHAR2(20), apl_cat_tp VARCHAR2(4), apl_ait VARCHAR2(15), apl_fnct_intgn_team VARCHAR2(50) ) ; drop SEQUENCE CST_AWR_CST_ID; CREATE SEQUENCE CST_AWR_CST_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE CST_ID ; CREATE SEQUENCE "CST_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 10001 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE DB_ID; CREATE SEQUENCE "DB_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 22 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE HI_ID; CREATE SEQUENCE "HI_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE MCH_ID; CREATE SEQUENCE MCH_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE MC_ID; CREATE SEQUENCE "MC_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE OS_ID; CREATE SEQUENCE "OS_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE PRJ_MNT_ID; CREATE SEQUENCE "PRJ_MNT_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop SEQUENCE PRJ_ID; CREATE SEQUENCE "PRJ_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop sequence schm_id; CREATE SEQUENCE SCHM_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE; drop sequence cls_id; CREATE SEQUENCE CLS_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop sequence eqp_id; CREATE SEQUENCE eqp_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop sequence apl_id; CREATE SEQUENCE apl_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE ; drop table apl; CREATE TABLE apl ( apl_ID VARCHAR2(4), apl_DSCR VARCHAR2(200), apl_abbr VARCHAR2(50), apl_nm VARCHAR2(40), apl_cmp_id VARCHAR2(50), apl_inf_tier VARCHAR2(20), apl_tier VARCHAR2(20), apl_rto VARCHAR2(4), apl_rpo VARCHAR2(4), apl_cat_tp VARCHAR2(4), apl_ait VARCHAR2(15), apl_fnct_intgn_team VARCHAR2(50) ) ; EOF
# Script Name..: restart_weblogic.ksh # Description..: Checks if weblogic process is running, if no start it # Author.......: Susheel Tamrakar # Date.........: April 2016 # Version......: v.01 # Modified By..: Susheel T. # Date Modified: # Comments.....: ############################################################################ #set -xv ############################################################################ # Apex server - lkcme179.kcm-p01.chp.bankofamerica.com # lkcme179.kcm-p01.chp.bankofamerica.com = New APEX server (version 5 using weblogic 12c) # Oracle user - DBADMD01 # important files # /var/opt/oracle/wls.env - contains environment variables for wls home and sample syntax # /var/opt/oracle/start_weblogic.sh - starts weblogic server # /var/opt/oracle/stop_weblogic.sh - stops weblogic server # /var/opt/oracle/restart_weblogic.sh - restarts weblogic server ############################################################################ echo "##################################################################" echo "# Welogic server process check " echo "##################################################################" # check if weblogic process is running if [ "$(ps -ef|grep weblogic| grep -v 'grep' |grep -v 'restart'| wc -l)" -gt 0 ] ; then echo "Weblogic application server process is running" echo " " echo " " echo " " else echo "Weblogic application server process is NOT running.....STARTING now and sleeping for 1 minute" echo " " echo " " echo " " /var/opt/oracle/start_weblogic.sh sleep 1m echo "Weblogic application server started, check process below..." echo " " echo " " echo " " echo "$(ps -ef|grep weblogic| grep -v 'grep'|grep -v 'restart')" fi
You must have a login to this system in order to use it, please contact mike.culp@gmail.com in order to obtain access.
Oracle Cloud Schema Dev environment
https://apex.oracle.com/doc51 (for 5.1)
Information Center: Oracle Application Express (APEX) (Doc ID 1418083.2)
Oracle RESTful Data Services FAQ (Doc ID 2085904.1)
APEX application DBAdmin
APEX Server Info
Change APEX Port
###################################################################
#!/bin/ksh
sqlplus -s "/ as sysdba" <<EOF
exec dbms_xdb.sethttpport(0);
commit;
exec dbms_xdb.sethttpport(8081);
commit;
EOF
###################################################################
Pull Registry Information
#################################################################
#!/bin/ksh
sqlplus -s "/ as sysdba" <<EOF
col comp_name format a35
col status format a12
select comp_name, status, version
from dba_registry
order by comp_name;
EOF
###################################################################
EPG Status
##################################################################
#!/bin/ksh
sqlplus -s "/ as sysdba" <<EOF
@/oracle/product/11.2.0/db_1/rdbms/admin/epgstat.sql
EOF
##################################################################
This is an APEX application that tracks databases and other information around
Objects include:
Database -
CREATE TABLE "DATABASE"
( "DB_ID" VARCHAR2(10),
"DB_NM" VARCHAR2(20),
"DB_DSCR" VARCHAR2(1000),
"DB_CMT" VARCHAR2(4000),
"DB_QADT" DATE,
"DB_INST_NM" VARCHAR2(15),
"DB_TP" VARCHAR2(3),
"DB_VER" VARCHAR2(15),
"DB_OS" VARCHAR2(4),
"DB_OS_VER" VARCHAR2(3),
"DB_UPG_VER" VARCHAR2(10),
"DB_UPG_STAT" VARCHAR2(3),
"DB_DBA_CRT" VARCHAR2(4),
"DB_APL" VARCHAR2(4),
"DB_DBA_CUR" VARCHAR2(4),
"DB_CRT_DT" DATE,
"DB_DT_NXT_UPG" DATE,
"DB_DT_UPG" DATE,
"DB_CMP" VARCHAR2(3),
"DB_DBA_PRIM" VARCHAR2(4),
"DB_DBA_SECD" VARCHAR2(4),
"DB_CST_ID" NUMBER,
"DB_CPU" NUMBER,
"DB_DSK_USED" NUMBER,
"DB_DSK_ALLOC" NUMBER,
"DB_MEM_PGA" NUMBER,
"DB_MEM_SGA" NUMBER,
"DB_MEM_TGT" NUMBER,
"DB_DBID" VARCHAR2(25),
"DB_BKP_FRQ" VARCHAR2(2000),
"DB_SVC_NM" VARCHAR2(50),
"DB_CAT_TP" VARCHAR2(35),
"DB_DBA_LU" VARCHAR2(25),
"DB_DBA_NU" VARCHAR2(25),
"DB_MCH_ID" VARCHAR2(10),
"DB_DT_DECOMM" DATE,
"DB_STORM" VARCHAR2(4000),
"DB_UNQ_NM" VARCHAR2(20),
"DB_AIT" VARCHAR2(20),
"DB_STO_DATA" NUMBER,
"DB_STO_SYS" NUMBER,
"DB_STO_FRA" NUMBER,
"DB_UPG_CMTS" CLOB,
"DB_PRCS" NUMBER,
"DB_SESS" NUMBER,
"DB_QST" VARCHAR2(4000),
"DB_HC_DBID" NUMBER,
"DB_STO_DATA_USD" NUMBER,
"DB_STO_FRA_USD" NUMBER,
"DB_STO_SYS_USD" NUMBER,
"DB_AUD_TP" VARCHAR2(50),
"DB_BCK" VARCHAR2(1),
"DB_SQLT" VARCHAR2(1),
"DB_CRTAB" VARCHAR2(1),
"DB_DDL" VARCHAR2(1),
"DB_ENV" VARCHAR2(1),
"DB_RMAN_ARGS" VARCHAR2(1),
"DB_RON_FO_TST" VARCHAR2(1),
"DB_RISK_RTG" VARCHAR2(4000),
"DB_NA_DT" DATE,
"DB_VFY_ASM" VARCHAR2(1),
"DB_DBCA" VARCHAR2(1),
"DB_DBCA_SL" VARCHAR2(4000),
"DB_TST_NWS" VARCHAR2(1),
"DB_ESM" VARCHAR2(1),
"DB_TST_CNCT" VARCHAR2(1),
"DB_PORT" VARCHAR2(4000),
"DB_HST" VARCHAR2(4000),
"DB_SVC_NM02" VARCHAR2(4000),
"DB_LOG_CLN" VARCHAR2(1),
"DB_LSN_CLN" VARCHAR2(1),
"DB_CRON_TXT" VARCHAR2(4000),
- stores the crontab entry text for this database
"DB_CLN_RUN" VARCHAR2(1),
"DB_MON_TST" VARCHAR2(1),
"DB_MRD" VARCHAR2(1),
"DB_SPM" VARCHAR2(1),
"DB_AWR" VARCHAR2(1),
"DB_INST_CG" VARCHAR2(3),
"DB_FOG" VARCHAR2(1),
"DB_SPT" VARCHAR2(1),
"DB_ORATAB" VARCHAR2(1),
"DB_90_CFG" VARCHAR2(1),
"DB_100_TBSPC" VARCHAR2(1),
"DB_QA_CMTS" CLOB,
"DB_STO_SAUX" NUMBER,
"DB_STO_SAUX_USD" NUMBER,
"DB_STO_USERS" NUMBER,
"DB_STO_USERS_USD" NUMBER,
"DB_STO_UNDO" NUMBER,
"DB_STO_UNDO_USD" NUMBER,
"DB_STO_TOOLS" NUMBER,
"DB_STO_TOOLS_USD" NUMBER,
"DB_STO_TEMP" NUMBER,
"DB_STO_TEMP_USD" NUMBER,
"DB_STO_ONREDO" NUMBER,
"DB_STO_ONREDO_USD" NUMBER,
CONSTRAINT "DATABASE_PK" PRIMARY KEY ("DB_ID") ENABLE
) ;
cluster
Tracking the problem tickets include:
incident table
inc_no
Ticket Number
Severity
Time Opened
Time Resolved
Total hrs worked
After hours worked
SLA Missed
Hostname
Resolved Group
Root Cause - drop down?
Duplicate? - has this ticket occured before
Detailed description
Brief Description
Work log
Comments
Detiled Resolution
This is the entry point into Oracle's APEX main site
APEX Download - This is Oracle site where the downloads are done from
APEX 5.0 Documentation - This is the link for documentation
ORDS This is the ORDS doc site