Introduction:
In this post, I'll share with you one of the most helpful tools I ever created, it will make database administration day to day activities more easier, faster and safer for you.
I named it DBA Bundle , it's a tar file contains a group of shell scripts, you can DOWNLOAD the latest version from this link:
https://www.dropbox.com/s/xn0tf2pfeq04koi/DBA_BUNDLE4.tar?dl=0
I've designed all scripts to be able to run on complicated environments, whereas there is one or more Oracle version / Oracle Home installed on the same machine.
All scripts inside the bundle can easily recognize the Oracle environment whether it's a Linux or Unix a long with databases version, Oracle homes.
All scripts can smartly handle user's wrong inputs as well.
Now let's get started with the top key features in this bundle ...
How to use the bundle:
First, download the bundle tar file and extract it under Oracle owner home directory, e.g. /home/oracle, each script inside this bundle is independent, in other words, the absence of any script will not affect the execution of other scripts.
Second, from the bundle extracted directory, run "aliases_DBA_BUNDLE.sh" script using "." command e.g. . aliases_DBA_BUNDLE.sh
It will add an alias for each script to the user's profile to make it easy for you to call any script from OS shell under any working directory using one command "alias" without the need to step under the bundle directory.
Aliases will be displayed to you a long with its description in a tabular format. no need to memorize it, in case you want to list it again just type "bundle" command.
Please note that when you run any interactive script in this bundle it will prompt you to select the database number from the displayed list in case you have more than one up & running database.
For a script like "aliases_DBA_BUNDLE.sh" when you select a database from the list, aliases like "alert, tns, bdump,..." will automatically point to the respective files to the database you have chosen from the list.
e.g. if you have two running instances (orcl & salesdb), and you want to open the alertlog of salesdb, just run "aliases_DBA_BUNDLE.sh" scripts, enter salesdb number from the displayed list, and then type alert/vialert to view salesdb alertlog files.
If you didn't run "aliases_DBA_BUNDLE.sh" script, for each time you want to call a script, you will need to step under the bundle location and then execute the script from there.
In this post, I'll share with you one of the most helpful tools I ever created, it will make database administration day to day activities more easier, faster and safer for you.
I named it DBA Bundle , it's a tar file contains a group of shell scripts, you can DOWNLOAD the latest version from this link:
https://www.dropbox.com/s/xn0tf2pfeq04koi/DBA_BUNDLE4.tar?dl=0
I've designed all scripts to be able to run on complicated environments, whereas there is one or more Oracle version / Oracle Home installed on the same machine.
All scripts inside the bundle can easily recognize the Oracle environment whether it's a Linux or Unix a long with databases version, Oracle homes.
All scripts can smartly handle user's wrong inputs as well.
Now let's get started with the top key features in this bundle ...
How to use the bundle:
First, download the bundle tar file and extract it under Oracle owner home directory, e.g. /home/oracle, each script inside this bundle is independent, in other words, the absence of any script will not affect the execution of other scripts.
Second, from the bundle extracted directory, run "aliases_DBA_BUNDLE.sh" script using "." command e.g. . aliases_DBA_BUNDLE.sh
It will add an alias for each script to the user's profile to make it easy for you to call any script from OS shell under any working directory using one command "alias" without the need to step under the bundle directory.
Aliases will be displayed to you a long with its description in a tabular format. no need to memorize it, in case you want to list it again just type "bundle" command.
Please note that when you run any interactive script in this bundle it will prompt you to select the database number from the displayed list in case you have more than one up & running database.
For a script like "aliases_DBA_BUNDLE.sh" when you select a database from the list, aliases like "alert, tns, bdump,..." will automatically point to the respective files to the database you have chosen from the list.
e.g. if you have two running instances (orcl & salesdb), and you want to open the alertlog of salesdb, just run "aliases_DBA_BUNDLE.sh" scripts, enter salesdb number from the displayed list, and then type alert/vialert to view salesdb alertlog files.
If you didn't run "aliases_DBA_BUNDLE.sh" script, for each time you want to call a script, you will need to step under the bundle location and then execute the script from there.
Scripts Description:
Now let me give you a brief description of each script in this bundle:
| Generic Aliases | Associated with the Default Selected Database |
| Shell Alias | Description |
| bundle | Set a database as a default database (all generic aliases will be associated to this database) |
| alert | Open the Database Alertlog with tail -f |
| vialert | Open the Database Alertlog with vi editor |
| raclog | Open the Clusterware/Oracle Restart Alertlog |
| sql | Open sqlplus ‘/ as sysdba’ |
| p | List all Running Database Instances (PMON Processes) |
| lsn | List Running Listeners |
| lis | Open listener.ora file with vi editor |
| tns | Open tnsnames.ora file with vi editor |
| pfile | Open the default instance PFILE with vi editor |
| spfile | Open the default instance SPFILE with view editor |
| oh | Go to $ORACLE_HOME directory |
| dbs | Go to $ORACLE_HOME/dbs |
| aud | Go to $ORACLE_HOME/rdbms/audit |
| bdump | Go to BACKGROUND_DUMP_DEST |
| network | Go to $ORACLE_HOME/network/admin |
| removebundle | Remove all the bundle Aliases from the System. (When you run it, let me know the reason) |
| Scripts With aliases | ||
| Script Name | Shell Alias | Description |
| table_info.sh | tableinfo | Show specific table’s important info (size, indexes, non indexed FK, constraints,...). |
| oradebug.sh | oradebug | Generate Hang Analysis report using oradebug tool in case of instance hang. (New script in V3.6) |
| active_sessions.sh | active | Show the current active sessions and their blocking sessions, along with long running operations + Current running jobs + long running queries. (New script in V3.6) |
| session_details.sh | session | List the Details of a specific user session. (If no input provided, it will list all sessions on the instance). |
| all_sessions_info.sh | sessions | List All connected sessions on all running instances [RAC DB] along with their distribution in details. |
| process_info.sh | spid | Show the DB Session details when providing its Unix PID. |
| sql_id_details.sh | sqlid | Show the details of a specific SQL STATEMENT by providing its SQL_ID and gives you the option of tuning it using SQL TUNING ADVISOR. Co-author: Farrukh Salman [Karkoor] |
| asmdisks.sh | asmdisks | Show ASM Diskgroups and their size, ASM disks, ASM disks mount points On OS. |
| tablespaces.sh | tbs | List All TABLESPACES, ASM Disk Groups and FRA (if was configured) allocated size and free space details. |
| datafiles.sh | datafiles | List All DATAFILES and their size. |
| export_data.sh | exportdata | Export Full DB|SCHEMA|TABLE data. (Gives you the option of using exp or expdp utility for the export). |
| RMAN_full.sh | rmanfull | Takes an online RMAN full backup for the database (gives you the option of number of channels/compressed/Encrypted backup type). |
| Archives_Delete.sh | archivedel | Delete all Archive logs older than (provided) number of days. |
| analyze_objects.sh | Analyze | Analyze All tables under a specific SCHEMA (using ANALYZE legacy command). |
| gather_stats.sh | gather | Gather STATISTICS on a SCHEMA or TABLE using DBMS_STATS. http://dba-tips.blogspot.ae/2014/09/script-to-ease-gathering- statistics-on.html |
| rebuild_table.sh | tablerebuild | Rebuild a table and its related indexes. |
| db_locks.sh | locks | List Blocking LOCKS details on the database (blocking users, blocking locks on objects, long running operations). |
| db_jobs.sh | jobs | List All database Jobs (dba_jobs + dba_scheduler_jobs + Auto Tune Tasks and current running jobs and their wait status) + job DDL & its history if provided its name/number. |
| invalid_objects.sh | invalid | List All invalid Objects on the DB + their compile statements. |
| biggest_100_objects.sh | objects | List the Biggest 100 Objects on the database. |
| object_size.sh | objectsize | Calculate any object size + its indexes size. |
| lock_user.sh | lockuser | Lock a specific DB User Account and expire the password. |
| unlock_user.sh | unlockuser | Unlock a specific DB User Account + the option of reset the account password. |
| audit_records.sh | audit | Retrieve AUDIT data for a DB user in a specific date or number of days back. http://dba-tips.blogspot.ae/2014/02/extract-oracle-audit-records- script.html |
| last_logon_report.sh | lastlogin | Show the last login date of ALL users in the database. |
| failed_logins.sh | failedlogin | Show the failed login attempts in the last provided n number of days. |
| parameter_val.sh | parm | Show the value of a Visible/Hidden initialization Parameter. |
| user_details.sh | userdetail | Generate the DDL Creation script for a DB user + its privileges important info about its schema and objects. |
| user_ddl.sh | userddl | Generate the DDL Creation script for a DB user + its privileges important info about its schema and objects. |
| object_ddl.sh | objectddl | Generate DDL script for a database Object + its granted permissions on it. |
| role_ddl.sh | roleddl | Generate DDL script for a database role. |
| start_tracing.sh | starttrace | Start TRACING an Oracle session activities in a trace file. http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html |
| stop_tracing.sh | stoptrace | Stop TRACING an already traced Oracle session & provide the session’s trace file and its TKPROFED log version. http://dba-tips.blogspot.ae/2014/02/script-to-trace-oracle-sesson.html |
| oracle_cleanup.sh | cleanup | Backup & Clean up All DBs & Listeners’ Logs. http://dba-tips.blogspot.ae/2014/02/oracle-logs-cleanup-script.html |
| Scripts Without aliases | |
| Script Name | Description |
| dbalarm.sh | Monitors ALERTLOGs of ALL Databases and Listeners log running on the server and instantly report ORA- errors and TNS- errors that appears in these logs to the DBA E-mail Address by sending a detailed email to the DBA along with monitoring CPU, Filesystem/FRA/Tablespaces utilization, blocking locks. (you have to modify this parameter in line number 27 to point to your E-mail Address): MAIL_LIST="youremail@yourcompany.com" Note: sendmail service should be configured on the server. *The best way to use this script is by schedule it to run in the crontab every 5 minutes (or less). For more details: http://dba-tips.blogspot.ae/2014/02/database-monitoring-script- for-ora-and.html |
| dbdailychk.sh | Perform the following health checks on all running databases on the server: # CHECKING ALL DATABASES ALERTLOGS FOR ERRORS. # CHECKING ALL LISTENERS ALERTLOGS FOR ERRORS. # CHECKING CPU UTILIZATION. # CHECKING FILESYSTEM UTILIZATION. # CHECKING TABLESPACES UTILIZATION. # CHECKING FLASH RECOVERY AREA UTILIZATION. # CHECKING ASM DISKGROUPS UTILIZATION. # CHECKING BLOCKING SESSIONS ON THE DATABASE. # CHECKING UNUSABLE INDEXES ON THE DATABASE. # CHECKING INVALID OBJECTS ON THE DATABASE. # CHECKING FAILED LOGIN ATTEMPTS ON THE DATABASE. # CHECKING AUDIT RECORDS ON THE DATABASE. # CHECKING CORRUPTED BLOCKS ON THE DATABASE. # CHECKING FAILED JOBS IN THE DATABASE. # CHECKING ACTIVE INCIDENTS. # CHECKING OUTSTANDING ALERTS. # CHECKING DATABASE SIZE GROWTH. # CHECKING OS / HARDWARE STATISTICS. # CHECKING RESOURCE LIMITS. # CHECKING RECYCLEBIN. # CHECKING CURRENT RESTORE POINTS. # CHECKING HEALTH MONITOR CHECKS RECOMMENDATIONS THAT RUN BY DBMS_HM PACKAGE. # CHECKING MONITORED INDEXES. # CHECKING REDOLOG SWITCHES. # CHECKING MODIFIED INITIALIZATION PARAMETERS SINCE THE LAST DB STARTUP. # CHECKING ADVISORS RECOMMENDATIONS: Replace youremail@yourcompany.com template with your e-mail address. You can also customize the defined thresholds as per your preferences under THRESHOLD section inside the script. Last step, Schedule the script to run in the crontab e.g. one time early morning: 0 6 * * * /home/oracle/dbdailychk.sa For more details: http://dba-tips.blogspot.ae/2015/05/oracle-database-health-check- script.html |
| delete_standby_archives.sh | Deletes the applied Archives on STANDBY DATABASES older than N hours (specified by the user). To be customized and scheduled from the crontab. For more details: http://dba-tips.blogspot.ae/2017/01/script-to-delete-applied- archivelogs-on.html |
| COLD_BACKUP.sh | Takes a COLD BACKUP of a specific database (But the beauty of this script once it take the cold backup it will generate another script to help you to restore the taken cold backup easily) This script will perform the following activities: shutdown the database, take a cold backup, create a restore script (in case you want to restore this cold backup later) then it will automatically startup the database. For more Details: http://dba-tips.blogspot.ae/2014/02/cold-backup-script.html |
| SHUTDOWN_All.sh | SHUTDOWN ALL running Databases & Listeners on The server. Keep away from children :-) |
| schedule_rman_full_bkp.sh | Takes an RMAN Full backup for a specific database. Can be scheduled in the crontab. You MUST adjust the variables/channels/maintenance section to match your environment. |
| schedule_rman_image_copy_bkp.sh | Takes an RMAN Image/Copy for a specific database. Can be scheduled in the crontab. You MUST adjust the variables/channels/maintenance sections to match your env. Why consider RMAN image backups in your backup strategy? the answer is in this link: http://dba-tips.blogspot.ae/2011/11/switch-database-to-rman-copy-backup-and.html |
| delete_applied_archives_on_standby.sh | Deletes the applied archivelogs on a standby DB. For More Details: http://dba-tips.blogspot.com/2017/01/script-to-delete-applied-archivelogs-on.html |
| configuration_baseline.sh | Collects all kind of configuration baseline data for OS and all running DATABASES to help you track and control the changes on your environment. For more details: http://dba-tips.blogspot.com/2016/12/configuration-baseline-script-for-linux.html |
| backup_ctrl_spf_AWR.sh | Backup Controlfile as (Trace/RMAN BKP), Backup SPFILE and Generate AWR for full day This script can be scheduled in the crontab to run once a day. Script options/variables must be modified to match your environment. New Starting from V4.1 |
| kill_long_running_queries.sh | Kill all queries running longer than 2.5 Hours(can be customized inside the script) by specific modules (to be specified inside the script) This script can be scheduled in the crontab. Script options/variables MUST be modified to get the killing criteria match your requirements. New Starting from V4.1 |
| check_standby_lag.sh | If you have a standby DB then you can use this script on Primary DB site to report to you any LAG happens between the primary and the standby DB. The variables section at the top of the script must be populated by you to match your environment or the script will not be able to run. This script can be scheduled in the crontab to run every 5 minutes. New Starting from V4.1 This link will show you how to use this script: http://dba-tips.blogspot.ae/2017/11/shell-script-to-check-lag-sync-status.html |
As I mentioned in each script, I'M SHARING THIS BUNDLE AND ITS SCRIPTS IN THE HOPE THAT IT WILL BE USEFUL FOR YOU, BUT WITHOUT ANY WARRANTY. ALL SCRIPTS IN THIS BUNDLE ARE PROVIDED "AS IS".
No one is perfect... that's why pencils have erasers.
You can download older versions from below links :
https://app.box.com/s/l4cmpxfrfy8t6emqrpgo [V. 1.1]
https://www.dropbox.com/s/mh0rk14alc69gqj/DBA_BUNDLE1_Sep2014.tar?dl=0 [V. 1.7 Sep2014]
https://www.dropbox.com/s/vrhslrg4l5xhzyb/DBA_BUNDLE1.tar?dl=0 [V. 1.8 Oct2014]
https://www.dropbox.com/s/lgrprfazgkeoxb5/DBA_BUNDLE2.tar?dl=0 [V. 2.0 08-May-2015]
https://www.dropbox.com/s/wnzvp49cyamqu66/DBA_BUNDLE2_Oct2015.tar?dl=0 [V. 2.2 Oct-2015]
https://www.dropbox.com/s/a1wn1j1squjf1qx/DBA_BUNDLE2_6Feb2016.tar?dl=0 [V2.3 Feb2016]
https://www.dropbox.com/s/lgrprfazgkeoxb5/DBA_BUNDLE2_25Apr2016.tar?dl=0 [V2.4 Apr2016]
https://www.dropbox.com/s/gxxb7jws8xngurj/DBA_BUNDLE3_10Oct2016.tar?dl=0 [V3.1 Oct2016]
https://www.dropbox.com/s/5wj52xqse9wcu6l/DBA_BUNDLE3_7Dec2016.tar?dl=0 [V3.3 Dec2016]
https://www.dropbox.com/s/c5tvgvvs3c8b749/DBA_BUNDLE3_3Jan2017.tar?dl=0 [V3.4 Jan2017]
Your suggestions, bug reporting and comments are most welcome :-)
Lastly, A special thank you to Abd El-Gawad Othman, without his support, suggestions and encouragement I wouldn't have been confident enough to share this bundle with you.
Không có nhận xét nào:
Đăng nhận xét