If you are trying to do an incomplete recovery (also called Point-In-Time Recovery, PITR), you may want to know a System Change Number (SCN) using a TIMESTAMP as an input, or vice versa. Oracle provides two functions: timestamp_to_scn and scn_to_timestamp for DBA to map, let’s describe the two functions below:SQL> DESC timestamp_to_scn;FUNCTION timestamp_to_scn RETURNS NUMBER Argument Name […]
Some DBAs will schedule a cron job to capture System Change Number (SCN) periodically to save as a Timestamp/SCN mapping reference. To achieve this goal, you can try the following statements.SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’;Session altered.SQL> COLUMN SYSTIMESTAMP FORMAT A35;SQL> SELECT systimestamp, current_scn FROM v$database;SYSTIMESTAMP […]
ORA-19809 We usually watch the space usage of log archive destination very closely because the database will suspend if the space is full. Furthermore, if the log archive destination is USE_DB_RECOVERY_FILE_DEST, then you must watch the usage of Fast Recovery Area (FRA) instead to prevent ORA-19809. When you meet the following error, you hit the […]
General backgroundBesides Connect-Time Failover by local naming, there is a function Transparent Application Failover (TAF) can do Run-Time Failover to enable user to continue his work transparently when connection failed over to other database (listener).There are two types of Run-Time Failover method, both methods are suitable for RAC architecture, but not for Data Guard, because […]
Oracle recommends to use DBMS_STATS instead of ANALYZE since 8i. Sometimes, we might get confused by the two similar tools. So I took some times to read several documents and summarize the key differences as below: Features DBMS_STATS ANALYZE Can gather statistics for CBO Can validate the structure of a segment Can list the chained […]
ORA-30036 This error ORA-30036 could be very serious in a situation that is described below and may make users panic: One day, a DBA was importing data and waited it for a long time. He suspected the operation was hanged, so he tried to cancel the operation by interrupting the job, but it didn’t work. […]
General backgroundThe following two error messages are very similar, as a DBA, you’ll probably meet them someday in the future. ORA-01653 is for table, ORA-01654 is for index. See below:Error: ORA-01653Text: unable to extend table %s.%s by %s in tablespace %s——————————————————————————-Cause: Failed to allocate an extent for table segment in tablespace.Action: Use ALTER TABLESPACE ADD […]
After you finish your shell scripting you’d like to try your scripts to see the result in your test environment. In the debug phase, you will generate a lot of echo message to see where’s problems, and maybe you will get some unrecognized files, like this:$ ls -iltotal 5 709233 -rw-r–r– 1 oracle […]
When you are a customer DBA at your client’s site to do routine db data collection or planned health check tasks, sometimes you have only lower privileges in their hosts, and their security policies cannot be compromised. Then you must find another workaround to make your solution real.In one case, when you finish a perfect shell […]
With a demanding database, you may want to know how to find out the most expensive sqls. Running on Oracle, we have Cost-Based Optimizer, the optimizer will choose the lowest cost of sql plan to run. So, if we could list the highest optimizer cost, we can map them to the most consuming sql statements.Oracle offers […]
