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 NUMBER Argument Name […]
Administration

How to Check Current System Change Number (SCN)

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

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

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

How to Resolve ORA-01653, ORA-01654 Unable to Extend Segment in Tablespace

Posted on
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 […]
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 -iltotal 5    709233 -rw-r–r–   1 oracle   […]