#!/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
Generating explain plans for 12.1
SQL Tuning Advisor
ADDM
SQLT
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 |
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.
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:
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:
Performance Information Centres provide access to various aspects of performance issues:
There are a number of troubleshooting guides that can help resolve various issues:
If the database is performing slowly see the following:
If you have problems with the concurrency of transactions, initially treat this as a "Slow Database Performance" issue and refer to the following:
If the database encounters what looks like a hang or a spin conditions refer to the following:
If an individual session appears to hang or spin conditions refer to the relevant sections in the following articles:
If sessions encounter locking conflicts refer to the following:
ORA-60 is an application error which usually occurs because a consistent locking strategy has not been followed throughout an application.
Please refer to the following articles for more information:
Please ensure that you are encountering none of the conditions in these articles.
For upgrades to 11g, there is a useful webcast entitled "11G Upgrade Best Practices" :
This recorded seminar provides an overview of tips and best practices for upgrading your database to 11G. It starts with general Challenges and Support Best Practices, but moves on to performance specifics such as AWR and STATSPACK, SQL Plan Management, and Real Application Testing.
This webcast can be found here:
For advice on planning your upgrade see:
For more general upgrade related information see:
To troubleshoot performance issues encountered after upgrading a database see:
There are numerous articles to help you debug issues with wait event contention. The most common of these being:
Document 1356828.1 FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
Document 1377998.1 Troubleshooting: Waits for Mutex Type Events
Document 1349387.1 Troubleshooting 'cursor: pin S wait on X' waits
Document 1357946.1 Troubleshooting 'library cache: mutex X' waits.
Document 1376916.1 Troubleshooting: "Log File Sync" Waits
Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention
Document 34405.1 WAITEVENT: "buffer busy waits" Reference Note
Document 223117.1 Troubleshooting I/O-related waits
Document 1415999.1 Troubleshooting: Virtual Circuit Waits
The failure to share cursors effectively can have a highly detrimental affect on a database. For example, having hundreds of versions of the same cursor is likely to be wasting space, is unlikely to promote good performance and, in extreme cases, can cause contention issues.
For issues linked to Cursor related waits see:
Also, see the following articles for more information and help with troubleshooting issues:
In cases where CPU usage of individual sessions or the whole database is high, refer to the following:
Note that High CPU usage can be caused by many factors including contention for cursors (see above), inefficient SQL and may be associated with excessive buffer reads etc.
If your system is waiting for the redo log files to synchronise with the disk, the following articles can help:
Document 1376916.1 Troubleshooting: "Log File Sync" Waits
Document 34592.1 WAITEVENT: "log file sync" Reference Note
Document 857576.1 How to Minimise Waits for 'Log File Sync'?
Document 1278149.1 High Log File Sync Wait Events, LGWR Posting Slow Write Times, Low IO Waits
Document 1064487.1 Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)
This Issue occurs when the database detects that a waiter has waited for a resource for longer than a particular threshold. The message "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" appears in the alert log and trace and systemstates are dumped.
Typically this is caused by two (or more) incompatible operations being run simltaneously. See:
Buffer Busy waits occur when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy".
Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.
Typical solutions are to look for SQL that accesses the blocks in question and determine if the repeated reads are necessary.
Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention
Document 34405.1 WAITEVENT: "buffer busy waits" Reference Note
Document 155971.1 Resolving Intense and "Random" Buffer Busy Wait Performance Problems:
Document 163424.1 How To Identify a Hot Block Within The Database Buffer Cache
SYSAUX is a mandatory tablespace in 10g which stores all auxiliary database metadata related to various product options and features. Of particular interest to Performance is the storage of Automatic Workload Repository (AWR) data:
Document 552880.1 General Guidelines for SYSAUX Space Issues
Document 1055547.1 SYSAUX Grows Because Optimizer Stats History is Not Purged
Document 387914.1 WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged
Document 554831.1 How to Control the Set of Top SQLs Captured During AWR Snapshot Generation
Document 1292724.1 SYSAUX Tablespace Grows Rapidly
OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing performance issues.
The Lite Onboard Monitor (LTOM) is a java program designed as a proactive, real-time diagnostic platform and provides real-time automatic problem detection and data collection.
The following articles can help with the interpretation of various diagnostics:
A series of Performance (and General Datbase topic) related webcasts, including topics such as SQLHC, SQLTXPLAIN and OSWBB, can be found here:
Performance and Scalability White Papers:
For White papers concerning Real Application Testing (RAT) and SQL Performance Analyzer (SPA) see:
Links to the main Tuning and Performance documentation:
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:
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
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