#!/bin/ksh
############################################################################
# Script Name..: 90_config_db_sga.ksh
# Description..: Modifies MRCTST01 parameters:
#                remote_listener
#                cpu_count
#                resource_manager_cpu_allocation
#                db_recovery_file_dest_size
#                sga_max_size
#                sga_target_size
#                processes
# Author.......: Michael Culp
# Date.........: 03/21/2013
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################

sqlplus -s "/ as sysdba" <<EOF
set echo on
set feedback on
set linesize 500

spool logs/90_config_db_sga.log

set timing on;

-- Gather existing parameters

show parameter sga_max_size
show parameter sga_target

create pfile='/tmp/dpodst01_before_sga_change' from spfile;

-- Register service
-- alter system register;

-- Modify Remote Listener

-- alter system set remote_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=10.40.190.124)(PORT=49147))(ADDRESS=(PROTOCOL=TCPS)(HOST=10.40.190.125)(PORT=49147))(ADDRESS=(PROTOCOL=TCPS)(HOST=10.40.190.126)(PORT=49147)))' scope=spfile sid='*';

-- Cage Instance

-- alter system set cpu_count=2 scope=spfile sid='*';
-- alter system set resource_manager_cpu_allocation=2 scope=spfile sid='*';

-- Set FRA

-- alter system set db_recovery_file_dest_size=50G scope=spfile sid='*';

-- Set SGA

alter system set sga_max_size=180G scope=spfile sid='*';
alter system set sga_target=96G scope=spfile sid='*';

create pfile='/tmp/dpodst01_after_sga_change' from spfile;

-- Set Processes

-- alter system set processes=1000 scope=spfile sid='*';

-- Set Parallel_Execution_Message_Size

-- alter system set parallel_execution_message_size=32768 scope=spfile sid='*';

-- show parameter remote_listener
-- show parameter cpu_count
-- show parameter resource_manager_cpu_allocation
-- show parameter db_recovery_file_dest_size
-- show parameter sga_max_size
-- show parameter sga_target
-- show parameter process

-- Alter existing default  tablespace sizes as per project requirement

spool off

EOF

# bounce DB after changing SGA_MAX_SIZE only no bounce needed if just changing
# SGA_TARGET
srvctl stop database -d MRCTST01
srvctl start database -d MRCTST01
srvctl status database -d MRCTST01
srvctl status service -d MRCTST01

SQL Tuning 12.1

Generating explain plans for 12.1

SQL Tuning 12.2

SQL Tuning Advisor

ADDM

SQLT

SQLT #1

Master Note: Database Performance Overview (Doc ID 402983.1)

Purpose
Questions and Answers
Pro-Active Problem Avoidance and Diagnostic Collection
Performance Information Centres
Troubleshooting Guides
Common Problem Topics
Slow Database Performance
Concurrency Issues
Database Hangs/Spins
Session Hangs/Spins
Locking Issues
Deadlock
Pre-Upgrade Planning
Post-Upgrade Performance Issue Resolution
Debugging Waits for Various Events
Library Cache/Mutex Contention/Cursor Type Events:
Other Types:
Common Causes of Performance issues
Cursor Sharing/High Version Counts for Cursors
High CPU usage
Issues With waits for 'log file sync'
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!
Buffer Busy/Cache Buffers Chains Latch waits
SYSAUX Issues
Performance Diagnostics References
General Diagnostics Overview
AWR/Statspack
10046 Trace
Systemstates/Hanganalyze
Errorstacks for Performance Issues
PStack
PL/SQL Profiler
OS Watcher
LTOM
Trace/Result Interpretation
Performance (and other) Webcasts
Performance and Scalability White Papers and Documentation
Interacting With Performance Support
Community: Database Tuning
References

Applies to:

Oracle Database - Standard Edition - Version 7.0.16.0 and later

Oracle Database - Personal Edition - Version 7.1.4.0 and later

Oracle Database - Enterprise Edition - Version 6.0.0.0 and later

Enterprise Manager for Oracle Database - Version 8.1.7.4 and later

Information in this document applies to any platform.

Purpose

This Document provides an overview reference to content that pertains to the resolution of Performance issues affecting the whole database.

For the Database Performance Frequently Asked Questions FAQ,information regarding Performance Related Features or  issues involving individual SQL statements, see:

Document 1360119.1 FAQ: Database Performance Frequently Asked Questions
Document 1361401.1 Where to Find Information About Performance Related FeaturesDocument 199083.1 Master Note: SQL Query Performance Overview
Document 1516494.2 Information Center: SQL Query Performance Overview
Document 398838.1 FAQ: SQL Query Performance - Frequently Asked Questions

Questions and Answers

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. For information on suggested preparations to avoid issues and in case diagnostics are required, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Performance Information Centres

Performance Information Centres provide access to various aspects of performance issues:

Document 1512292.2 Information Center: Database Performance Overview
Document 1516494.2 Information Center: SQL Query Performance Overview

Troubleshooting Guides

There are a number of troubleshooting guides that can help resolve various issues:

Document 1543445.2 Troubleshooting Assistant: Troubleshooting Performance Issues
Document 1542678.2 Troubleshooting Assistant: SQL Performance IssuesDocument 1528847.1 Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
Document 60.1 Troubleshooting Assistant: Oracle Database ORA-00060 Errors on Single Instance (Non-RAC) Diagnosing Using Deadlock Graphs in ORA-00060 Trace Files

Common Problem Topics

Performance Diagnostics References

Performance (and other) Webcasts

A series of Performance (and General Datbase topic) related webcasts, including topics such as SQLHC, SQLTXPLAIN and OSWBB, can be found here:

Document 1456176.1 Oracle Database Advisor Webcast Archives

Performance and Scalability White Papers and Documentation

Performance and Scalability White Papers:

http://www.oracle.com/technetwork/database/focus-areas/performance/index.html

For White papers concerning Real Application Testing (RAT) and SQL Performance Analyzer (SPA) see:

Document 1546337.1 Real Application Testing (RAT): Recommended White Papers

Links to the main Tuning and Performance documentation:

Document 1195363.1 Database Performance and SQL Tuning Documentation on OTN

Interacting With Performance Support

Document 210014.1 How to Log a Good Performance Service Request
Document 166650.1 Working Effectively With Global Customer Support

Community: Database Tuning

A community has been set up for My Oracle Support (MOS) users moderated by Oracle. The goal of this community is to exchange database related Tuning knowledge and concepts. The community can be found via the following article:

Document 1383594.1 Collaborate With MOS Database Tuning Community Members

 

References

NOTE:1320966.1 - Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results

NOTE:461228.1 - The LTOM Graph FAQ

NOTE:175006.1 - Steps to generate HANGANALYZE trace files (9i and below)

NOTE:18251.1 - OERR: ORA 60 "deadlock detected while waiting for resource"

NOTE:452358.1 - How to Collect Diagnostics for Database Hanging Issues

NOTE:199083.1 - * Master Note: SQL Query Performance Overview

NOTE:223117.1 - Troubleshooting I/O-related waits

NOTE:68738.1 - No Response from the Server, Does it Hang or Spin?

NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged

NOTE:390374.1 - Oracle Performance Diagnostic Guide (OPDG)

NOTE:392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum'

NOTE:166650.1 - Working Effectively With Support Best Practices

NOTE:461050.1 - The LTOM Graph (LTOMg) User Guide

NOTE:1357946.1 - Troubleshooting 'library cache: mutex X' waits.

NOTE:352648.1 - How to Diagnose High CPU Usage Problems to the Module Level

NOTE:554831.1 - How to Control the Set of Top SQLs Captured During AWR Snapshot Generation

NOTE:398838.1 - FAQ: SQL Query Performance - Frequently Asked Questions

NOTE:465787.1 - How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g

NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

NOTE:39817.1 - Interpreting Raw SQL_TRACE output

NOTE:62365.1 - Troubleshooting "ORA-00060 Deadlock Detected" Errors

NOTE:785351.1 - Oracle 11gR2 Upgrade Companion

NOTE:857576.1 - How to Minimise Waits for 'Log File Sync'

NOTE:94224.1 - FAQ- Statspack Complete Reference

NOTE:70609.1 - How To Display Information About Processes on SUN Solaris

NOTE:977975.1 - Diagnose LTOM Connection Problems

NOTE:1055547.1 - SYSAUX Grows Because Optimizer Stats History is Not Purged

NOTE:34592.1 - WAITEVENT: "log file sync" Reference Note

NOTE:352363.1 - LTOM - The On-Board Monitor User Guide

NOTE:601807.1 - Oracle 11gR1 Upgrade Companion

NOTE:210014.1 - How to Log a Good Performance Service Request

NOTE:1377446.1 - * Troubleshooting Performance Issues

NOTE:243755.1 - Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data

NOTE:1528847.1 - Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade

NOTE:1392633.1 - Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results

NOTE:466181.1 - Oracle 10g Upgrade Companion

NOTE:1361401.1 - Where to Find Information About Performance Related Features

NOTE:1362329.1 - How To Investigate Slow or Hanging Database Performance Issues

NOTE:1363422.1 - Automatic Workload Repository (AWR) Reports - Start Point

NOTE:1364257.1 - How to Collect Errorstacks for use in Diagnosing Performance Issues.

NOTE:163424.1 - How To Identify a Hot Block Within The Database Buffer Cache.

NOTE:1360119.1 - * FAQ: Database Performance Frequently Asked Questions

NOTE:552880.1 - General Guidelines for SYSAUX Space Issues

NOTE:1278149.1 - Intermittent Long 'log file sync' Waits, LGWR Posting Long Write Times, I/O Portion of Wait Minimal

NOTE:1195363.1 - Database Performance and SQL Tuning Documentation on OTN

NOTE:121779.1 - Taking Systemstate Dumps when You cannot Connect to Oracle

NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

NOTE:1415999.1 - Troubleshooting: Virtual Circuit Waits

NOTE:1064487.1 - Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)

NOTE:438452.1 - Performance Tools Quick Reference Guide

NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s)

NOTE:461052.1 - LTOM System Profiler - Sample Output

NOTE:278316.1 - Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "

NOTE:15476.1 - FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors

NOTE:1392319.1 - Master Note: Locks, Enqueues and Deadlocks (ORA-00060)

NOTE:155971.1 - Resolving Intense and "Random" Buffer Busy Wait Performance Problems

NOTE:1376916.1 - Troubleshooting: "log file sync" Waits

NOTE:1292724.1 - Suggestions if your SYSAUX Tablespace grows rapidly or too large

NOTE:62354.1 - Waits for 'Enq: Tx - Row Lock Contention' - Wait Scenario Examples

NOTE:1377998.1 - Troubleshooting: Waits for Mutex Type Events

NOTE:1383594.1 - Collaborate with MOS Database Tuning Community Members

NOTE:762540.1 - Consolidated Reference List Of Notes For Migration / Upgrade Service Requests

NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events

NOTE:1509919.1 - Master Note for Database Error ORA-00060 "deadlock detected while waiting for resource"

NOTE:781198.1 - Diagnostics for Database Performance Issues

NOTE:273646.1 - How to Diagnose High CPU Utilization of ORACLE.EXE in Windows Environment

NOTE:1342917.1 - Troubleshooting 'latch: cache buffers chains' Wait Contention

NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.

NOTE:296377.1 - Troubleshooting: High Version Count Issues

NOTE:301137.1 - OSWatcher Black Box (Includes: [Video])

NOTE:33453.1 - Locking and Referential Integrity

NOTE:34405.1 - WAITEVENT: "buffer busy waits" Reference Note

RMAN Table Level Recovery

TTSRMAN

Host common is a library of functions that will help to create, maintain, hosts involved with Oracle databases as well as other types of servers

This file should also have the

List of functions

HSTCREATE => hst_common.ksh
HSTCREATE - Library of creation scripts for creating and maintaining hosts
Linux_common.ksh should be used in this capacity to link in the necessary low-level scripts to perform

/oracle/admin/scripts/hstcreate - current production copy of the scripts
/oracle/admin/scripts/hstcreate/dev - development stuff

Using DBAdmin there are several functions that can be utilized for these purposes

hst_create.ksh -

initapex()
{

}

This function shows the banner of the script and the script name along with the arguments file
disp_banner_hst_cr ()
{
echo
echo "Host create script..........: hst_create.ksh"
echo "Host create arguments file..: hst_create.args"
echo
}


function next_menu
{
echo
echo "# ----------------------------------------------------------------------- #"
echo "[ ...  Press any key to continue ... ]"
read next
}

init_apex ()
{

# Initialize the database access to DBAdmin
# Set this up on a separate database
export BRAIN1D='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=49125))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DBADMD01)))'
export DBADM='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=49125))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DBADMD01)))'
export DBADM42='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=49125))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=BRAIN1D)))'

export UsrPwd='hc_core/somepassword'
export UsrPwd2='dba01/somepassword'

}

host_apex_chk ()
{
####################################################################
# prereq is init_apex function should be included
# Set the hostname then search for the host record in APEX
# once you get the DBAdmin host record in mach_id
####################################################################
# grab the hostname
hst_nm=`hostname`

# echo $hst_nm
mach_id=`${ORACLE_HOME}/bin/sqlplus -s $UsrPwd2@$BRAIN1D <<EOF
        set head off feed off pages 0 trimspool on
        select MCH_ID from dba01.machine where MCH_NM=upper('$hst_nm');
--        select MCH_ID from dba01.machine;
--        exit;
EOF`

mch_nm=`${ORACLE_HOME}/bin/sqlplus -s $UsrPwd2@$BRAIN1D <<EOF
        set head off feed off pages 0 trimspool on
        select MCH_NM from dba01.machine where MCH_NM=upper('$hst_nm');
--        exit;
EOF`

# Uncomment the next line for debugging purposes
# echo "DBAdmin record no...: "$mach_id

}


This is a directory checking function, if the directory doesn't exists it creates it

chk_gen_dir ()
{
############################################################################
# Check to see if the directory to generate the files exists
# added /scripts to the end of the gendir to check
############################################################################
if [ ! -d $gendir/scripts ] ; then
#   echo "Directory "${gendir}" does not exist"
   print "\nDirectory ${gendir}/scripts does not exist"
   print "\nDirectory ${gendir}/scripts needs to be created "
   ans=
   while [ -z $ans ]
   do
      echo -n "Create directory? ${gendir}/scripts "
      read ans
   done
   if [[ $ans =~ ^[Yy]$ ]]
   then
      print "\n...Creating directory...."
#      mkdir ${gendir}/scripts
#      mkdir ${gendir}/scripts
      print "\n...Not Creating directory in test mode...."
   else
      print "\n...Not Creating directory...."
      print "\nExiting program"
      exit
   fi
else
   print "\nDirectory ${gendir}scripts exists"
   echo
   ls ${gendir}/scripts

   ans=
   while [ -z $ans ]
   do
      echo -n "Do you wish to overwrite what is already generated? "
      read ans
   done

   if [[ $ans =~ ^[Yy]$ ]]
    then
      print "\n...Continuing..............................."
   else
      print "\n....Exiting program without creating scripts"
      exit
   fi

   ans=
   while [ -z $ans ]
   do
      echo -n "Proceed to script creation? "
      read ans
   done

   if [[ $ans =~ ^[Yy]$ ]]
    then
      print "\n............................................"
   else
      print "\n....Exiting program without creating scripts"
      exit
   fi

fi


if [ ! -d $gendir/logs ] ; then
#  echo "Directory "${gendir}/logs" does not exist"
   print "\nDirectory ${gendir}/logs does not exist"
   print "\nDirectory ${gendir}/logs needs to be created "
   ans=
   while [ -z $ans ]
   do
      echo -n "Create directory? ${gendir}/logs "
      read ans
   done
   if [[ $ans =~ ^[Yy]$ ]]
   then
      print "\n...Creating directory...."
#      mkdir ${gendir}/logs
#      mkdir ${gendir}/logs
      print "\n...Not Creating directory in test mode...."
   else
      print "\n...Not Creating directory...."
      print "\nExiting program"
      exit
   fi
else
   print "\nDirectory ${gendir}/logs exists"
   echo
   ls ${gendir}/logs

   ans=
   while [ -z $ans ]
   do
      echo -n "Do you wish to overwrite what is already generated? "
      read ans
   done

   if [[ $ans =~ ^[Yy]$ ]]
    then
      print "\n...Continuing..............................."
   else
      print "\n....Exiting program without creating scripts"
      exit
   fi

   ans=
   while [ -z $ans ]
   do
      echo -n "Proceed to script creation? "
      read ans
   done

   if [[ $ans =~ ^[Yy]$ ]]
    then
      print "\n............................................"
   else
      print "\n....Exiting program without creating scripts"
      exit
   fi

fi

pauseit
clear
}




cr_hst_crontab ()
{
############################################################################
# Create the crontab entries
############################################################################

echo "...Creating crontab file entries......."
echo > crontab_${db_assn_name}.txt
cat << EOS >> crontab_${db_assn_name}.txt
Text goes here
EOS
}


cr_sysctl ()
{
echo "...Creating sysctl.conf recommendations......."
####################################################################################################
# Create the cr_<database name>_dbca.ksh script /oracle/admin/scripts/rspfiles
####################################################################################################
# echo > cr_${host_name}_sysctl.ksh
cat << EOS > cr_${host_name}_sysctl.conf
#!/bin/ksh
############################################################################
# Script Name..:
# Description..:
# Author.......: $auth
# Date.........: $use_date
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
#              : alter session set current should be used to change schemas
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 1

# Controls whether core dumps will append the PID to the core filename
# Useful for debugging multi-threaded applications
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 1

# Controls whether core dumps will append the PID to the core filename
# Useful for debugging multi-threaded applications
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
net.ipv4.conf.all.log_martians = 0
net.ipv4.conf.default.log_martians = 0
net.ipv4.tcp_keepalive_time = 3600
kernel.panic_on_oops = 1
kernel.panic = 5
#ORACLE CONFIG
#fs.aio-max-nr = 1048576
fs.aio-max-nr = 3145728
kernel.shmmax = 17179869184
kernel.shmmni = 4096
kernel.shmall = 8388608
#kernel.sem = 250 32000 100 128
kernel.sem = 250 64000 100 256
net.core.rmem_default = 4194304
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
net.ipv4.tcp_rmem = 4194304 4194304 4194304
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.ip_local_port_range = 1024 65000
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 15
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.hugetlb_shm_group = 0
vm.nr_hugepages = 2048
net.ipv4.ip_local_port_range = 9000 65500
fs.file-max = 6815744

EOS

# Move previous file to the correct directory
# cp cr_${db_assn_name}_dbca.ksh /oracle/admin/scripts/rspfiles

}


ck_cls ()
{

############################################################################
# This will check the cluster
############################################################################

if [[ "$database_id" = "Database Not In APEX" ]]; then
   ans=
   while [ -z $ans ]
   do
      echo
      echo -n "Add database record to APEX? "
         read ans
   done
   if [[ $ans =~ ^[Yy]$ ]]
   then
      echo
      print "\n....Adding APEX database record....."

${ORACLE_HOME}/bin/sqlplus -s $UsrPwd2@$BRAIN1D <<EOF
      insert into dba01.database
         (DB_ID,
          DB_NM,
          DB_DSCR,
          DB_CRT_DT,
          DB_UNQ_NM)
      values
         (DB_ID.NEXTVAL,
          '$db_assn_name',
          'Database added by DBAdmin ddl_create please update columns ',
          sysdate,
          '$db_assn_nm');
      commit;
EOF
      print "\n....APEX database record added....."
database_id=`${ORACLE_HOME}/bin/sqlplus -s $UsrPwd2@$BRAIN1D <<EOF
        set head off feed off pages 0 trimspool on
        select DB_ID from dba01.database where DB_NM='$db_assn_name';
        exit;
EOF`
        echo $database_id
   else
      echo
      print "\n....Continuing without adding record to APEX........."
      # exit
   fi
fi


# Check to see if the directory to generate the files exists
############################################################################
if [ ! -d $gendir ] ; then
   print "\nDirectory ${gendir} does not exist"
   print "\nDirectory ${gendir} needs to be created "
   ans=
   while [ -z $ans ]
   do
      echo -n "Create directory? ${gendir} "
      read ans
   done
   if [[ $ans =~ ^[Yy]$ ]]
   then
      print "\n...Creating directory...."
      mkdir ${gendir}
      mkdir ${gendir}/scripts
   else
      print "\n...Not Creating directory...."
      print "\nExiting program"
      exit
   fi
else
   print "\nDirectory ${gendir} exists"
   echo
   ls ${gendir}/scripts

   ans2=
   while [ -z $ans2 ]
   do
      echo -n "Do you wish to overwrite what is already generated? "
      read ans2
   done

   if [[ $ans2 =~ ^[Yy]$ ]]
    then
      print "\n...Continuing..............................."
   else
      print "\n....Exiting program without creating scripts"
      exit
   fi


   ans2=
   while [ -z $ans2 ]
   do
      echo -n "Proceed to script creation? "
      read ans2
   done

   if [[ $ans2 =~ ^[Yy]$ ]]
    then
      print "\n............................................"
   else
      print "\n....Exiting program without creating scripts"
      exit
   fi

fi

gendir=${gendir}/scripts

echo " "
echo " "


# You can bale out here for whatever reason
#########################################################################
ans=
while [ -z $ans ]
do
   echo -n "Really Proceed with script creation ? "
      read ans
done
if [[ $ans =~ ^[Yy]$ ]]
then
   print "\n....Creating scripts....."
else
   print "\n....Exiting program without creating scripts"
   exit
fi
############################################################################

############################################################################
# Create the directory /oracle/admin/<database name>/scripts
#
# Create the directory for this to all go into
# mkdir -p /oracle/admin/$db/scripts
# mkdir -p /oracle/admin/$db/scripts/logs
# cd /oracle/admin/scripts/mrc/ddltest/$appabrl
# /oracle/admin/scripts/mrc/ddltest/testddlcr
############################################################################

print "\n.....Changing to directory ${gendir} "
cd ${gendir}
}



cr_tmplt ()
{
echo "...Creating template file......."
# Create the
############################################################################
echo > template.ksh
cat << EOS >> template.ksh

EOS
}

function next_menu
{
echo
echo "# ----------------------------------------------------------------------- #"
echo "[ ...  Press any key to continue ... ]"
read next
}

connect / as sysdba
spool moreinfo.log
col username format a15
col account_status format a20
select username, account_status from dba_users
where username = 'ANONYMOUS';
set long 100000 pagesize 0
select XDBUriType('/xdbconfig.xml').getXML() from dual;
spool off

SQL Dev Compare

Datapump parms Table_exists

Inspecting Elements

This content is password protected. To view it please enter your password below:

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram