Flashback

How to Enable Flashback Database

Posted on
Flashback Database Flashback Database can rewind an entire database back to a state of point-in-time, just before operation failures which involved whole database. The result is the same as database point-in-time recovery (DBPITR) performed by RMAN, but rather simple to operate. If you want to save a recently truncated table, Flashback Table helps none, but […]
Fast Recovery Area

How to Enable Fast Recovery Area (FRA)

Posted on
Fast Recovery Area (FRA) is a very convenient feature for DBA to backup and restore database. It could happen on us to leave FRA option unchecked during database installations. And we may want to enable fast recovery area after the database has been created. Enable Fast Recovery Area There’re 3 statements make your database starting […]
Performance

How to Check Free Memory in SGA Pools

Posted on
The dynamic view gv$sga can tell you how much memory he occupied from OS. They won’t change very often. The real available memory of SGA is provided on gv$sgastat, you can check the free memory of every pool like this.SQL> column “Free (MB)” format 9,999.99;SQL> SELECT inst_id, pool, name, bytes/1024/1024 “Free (MB)” FROM gv$sgastat WHERE name=’free […]
Performance

How to Check SGA Components Memory Distribution on Database 9i

Posted on
For database 9i, there is no dynamic view gv$sgainfo like 10g and 11g that can represents current memory distribution of every component. Another dynamic view gv$sga_dynamic_components might help, but the information is incomplete.If you have memory issues on database 9i, you could seek gv$sgastat for help, which can provides more detailed information AFTER some processes and combinations. Let’s […]
Tablespace

How to Check Tablespace Usage By SQL

Posted on
If someday, you have no GUI tools around, the only tool you got is sqlplus, a text_based tool, everything must be back to basic. How basic? Every routine jobs including monitoring, maintenance and troubleshooting must be composed as statements and scripts one by one. Checking all tablespaces usage is one of the jobs, very routine, […]
Linux

How to Write Control File into Raw Device

Posted on
Since raw devices cannot be read/write as a normal file operation, you cannot write a controlfile directly into a raw device. I guess DBA will have the problem on 9i or 10g, because 11g database does not support raw devices any longer, but 11g ASM does, and 11g ASM can take care itself through ASMCMD. For […]
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             […]