#!/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