Category: Database Troubleshooting
-
Generating SQL to Create an External Table- External Tables
If you are currently working with SQL*Loader and want to convert to using external tables, you can use the SQL*Loader to generate the SQL required to create the external table, using the EXTERNAL_TABLE option. A small example will help demonstrate this process. Suppose you have the following table DDL: SQL> create table books (book_id number,…
-
Verifying the Integrity of RMAN Backups- Automation and Troubleshooting
As part of your backup-and-recovery strategy, you should periodically validate the integrity of the backup files. This is also included as part of using RMAN to back up the database, but a separate job can run against them to validate for restore. RMAN provides a RESTORE…VALIDATE command that checks for physical corruption within the backup…
-
List Contents of Dump Files- External Tables
Data Pump has a robust method of creating a file that contains all the SQL that is executed when an import job runs. Data Pump uses the DBMS_METADATA package to create the DDL that you can use to re-create objects in the Data Pump dump file. Use the SQLFILE option of Data Pump import to…
-
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…
-
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…
-
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…
-
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.…
-
Stopping a Job- Automation and Troubleshooting
If you have a job that has been running for an abnormally long period of time, you may want to abort it. Use the STOP_JOB procedure to stop a currently running job. This example stops the RMAN_BACKUP job while it is running: SQL> exec dbms_scheduler.stop_job(job_name=>’rman_backup’); The STATUS column of DBA_SCHEDULER_JOB_LOG will show STOPPED for jobs…
-
Checking for Archivelog Destination Fullness- Automation and Troubleshooting
Sometimes DBAs and SAs do not adequately plan and implement a location for storing archivelog files on disk, or there is more growth and activity than expected. In these scenarios, it is sometimes convenient to have a script that checks for space in the primary location and that sends out warnings before the archivelog destination…
-
Specifying a Query- External Tables
You can use the QUERY parameter to instruct Data Pump to write to a dump file only rows that meet a certain criterion. You may want to do this if you’re re-creating a test environment and only need subsets of the data. Keep in mind that this technique is unaware of any foreign key constraints…