Category Archives: DBAdmin

Update page process

This is the code for the select on the region for instances


Region select code
select * from hc_instance where :P612_HC_DB_ID = db_id
 

This is the code for the Page Process for the insert, here we are inserting a record, then updating the value for the next time it refreshes the record.

/* Inserts / Updates a machine row */
declare
v_date        date     := sysdate;
e_date        date     := to_date('01-JAN-4000','DD-MON-YYYY');
begin
insert into instance
columns (
in_id,
in_nmb,
in_name,
mch_id,
db_id,
in_mem_sga_tgt,
in_mem_sga_max,
in_mem_tgt,
in_mem_max,
in_mem_pga,
in_cpu
)
values (
in_id.NEXTVAL,
:P612_DB_INST_NBR,
:P612_DB_INST_NM,
:P612_DB_MCH,
:P612_DB_ID,
:P612_DB_MEM_SGA_TGT,
:P612_DB_MEM_SGA_MAX,
:P612_DB_MEM_TGT,
:P612_DB_MEM_MAX,
:P612_DB_MEM_PGA,
:P612_DB_CPU
);
update database
set db_mch_id = ' '
where db_id   = :P612_DB_ID;
end;

bounce_apex.ksh

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

105_cr_dba01.ksh


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

DBAdmin Databases Section 02

DBAdmin Database(s)

The DBAdmin system at the core is just a simple database an can be used in and of itself. There are approximately 100+ tables that make up the Oracle DBAdmin database and it currently runs on a VM and non-RAC version of Oracle.  Each database is accessed by the Oracle database through the use of gateways that allow seemless access to the database(s) housed on remote systems. A free web-based application environment that comes imbedded in every Oracle database since 10g called APEX, was used to provide a reporting and user friendly GUI to the data. Currently this tool only runs on the Oracle database platform.

Interaction between various scripts and other hosts, as well as other databases can easily interact with Oracle as such interactions between Oracle and other platforms are very common and easily configured.