Administration

How to Map Timestamp to SCN or Vice Versa

Posted on
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 […]
Administration

How to Check Current System Change Number (SCN)

Posted on
System Change Number 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 […]
TNS Name

How to Implement Transparent Application Failover (TAF) by Local Naming

Posted on
Transparent Application Failover (TAF) Besides Connect-Time Failover by local naming, there is a function called 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 […]
Performance

ANALYZE vs DBMS_STATS

Posted on
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 […]
Data File

How to Resolve ORA-01653 Unable to Extend Table in Tablespace

Posted on
ORA-01653 The 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. Essentially, they are the same thing: Error:  ORA-01653 Text:   unable to extend table %s.%s by %s in tablespace %s ——————————————————————————- Cause:  Failed to allocate an extent […]
Linux

How to Remove No-Name or Empty-Name Files

Posted on
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 -il total 5     709233 -rw-r–r– […]