Author: Jodean Baker
-
Using top – Automation and Troubleshooting
Another quick utility for identifying resource-intensive processes is the top command. Use this tool to quickly identify which processes are the highest consumers of resources on the server. By default, top will repeatedly refresh every three seconds with information regarding the most CPU-intensive processes. $ top And you can run it batch mode and send…
-
Database Troubleshooting- Automation and Troubleshooting
So far there have been scripts provided to help avoid issues with the database. Even in the brief discussion about Autonomous Database, self-repairing was discussed as there are tasks that can be done automatically to fix and have the database up and available. But there are things that might need to be investigated such as…
-
Viewing Job Details- Automation and Troubleshooting
To view details about how a job is configured, query the DBA_SCHEDULER_JOBS view. This query selects information for the RMAN_BACKUP job: SQL> select job_name ,last_start_date ,last_run_duration ,next_run_date ,repeat_interval from dba_scheduler_jobs where job_name=’RMAN_BACKUP’; Each time a job runs, a record of the job execution is logged in the data dictionary. To check the status of a…
-
External Tables with Oracle Cloud Database- External Tables
Cloud databases let us easily use files from cloud storage, and this is just a quick example of leveraging files in your cloud environment with your database. You don’t have to have a directory created to use external tables, but you need to know the URI for the file that is the namespace and tenancy…
-
Identifying System Bottlenecks – Automation and Troubleshooting
Whenever there are application performance issues or availability problems, seemingly (from the DBA’s perspective), the first question asked is, “What is wrong with the database?” Regardless of the source of the problem, the DBA is often required to look if the database is behaving well. Approaching these issues when looking at the database and system-wide…
-
Checking for Locked Production Accounts- Automation and Troubleshooting
Usually having a database profile should be in place that specifies that a database account become locked after a designated number of failed login attempts. For example, set the DEFAULT profile FAILED_LOGIN_ATTEMPTS to 5. Sometimes, however, a rogue user or developer will attempt to guess the production account password and, after five attempts, lock the…
-
Tablespace Level- External Tables
A tablespace-level export/import operates on objects contained within specific tablespaces. This example exports all objects contained in the USERS tablespace: $ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=tbsp.dmp tablespaces=users You can initiate a tablespace-level import by using a full export but specifying the TABLESPACES parameter: $ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=full.dmp tablespaces=users A tablespace-level import will attempt to create…
-
Using AWR – Automation and Troubleshooting
An AWR report is good for viewing the entire system’s performance and identifying the top resource-consuming SQL queries. Run the following script to generate an AWR report: SQL> @?/rdbms/admin/awrrpt You can run the AWR reports from the PDB or CDB, root container. The reports will be from PDB using AWR_PDB views in that PDB, or…
-
Loading a Regular Table from the External Table- External Tables
Now, you can load data contained in the external table into a regular database table. When you do this, you can take advantage of Oracle’s direct-path loading and parallel features. This example creates a regular database table that will be loaded with data from the external table: SQL> create table exa_info( exa_id NUMBER ,machine_count NUMBER…
-
Locating the Alert Log and Trace Files – Automation and Troubleshooting
The default alert log directory path has this structure: $ORACLE_HOME/rdbms/log Or find it easily with the show parameter command: SQL> show parameter background You can override the default directory path for the alert log by setting the DIAGNOSTIC_DEST initialization parameter. Usually, the db_unique_name is the same as the instance_name, but it depends on the environment.…