Creating Restore Point

Steps to do flashback:

1)COnfigure the three parameters.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /decoopdbdmmk1/oracle/arch/div03/fra

db_recovery_file_dest_size big integer 2G

SQL> show parameter flashback

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440

2)SQL> select flashback_on from V$database;

FLASHBACK_ON

NO

if the value is YES then go to step 3 else

SQL> alter database flashback on;

Database altered.

3)SQL> Create table vijay.flash_t (id int);

Table created.

SQL> begin

2 for i in 1..100

3 loop

4 insert into vijay.flash_t values(i);

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

4)SQL> create restore point vijay gUARANTEE flashback database;

Restore point created.

5)SQL> truncate table vijay.flash_t;

Table truncated.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size 2232640 bytes

Variable Size 2113932992 bytes

Database Buffers 1073741824 bytes

Redo Buffers 16928768 bytes

Database mounted.

6)SQL> flashback database to restore point vijay;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

7)SQL> select count(1) from vijay.flash_t;

COUNT(1)

100

Using Temp tablespace Groups

I wanted to share the experience on using temp tablespace groups that I proposed on our last staff meeting.

Abstract:

Test advantages of using temporary tablespace group.

Steps performed for testing:

• Created three temporary tablespaces each of 8GB with AUTOEXTEND OFF and assigned them to a single tablespace group. Now the tablespace group is of total size 8X3=24GB

• Created a session and ran a SQL query which will use temp tablespaces. Also making sure this query will need space more than the size of one tablespace (i.e.) 8GB. I used a order by on a big table.

• Created multiple sessions from a single database user to use temporary tablespace group.

• Created multiple sessions of different users to use temporary tablespace group.

Result Obtained:

• Single SQL query could not span multiple temporary tablespaces for sorting. Temporary segments do not span tablespaces. Although many blogs says it will and I did not find that happen.

• Different sessions of same user used different tablespaces in the group at the same time. (i.e.) Each session with its own SQL query runs on different tablespaces.

• Different user sessions used different tablespaces in the group.

Conclusion:

• Multiple default temporary tablespace will ease maintenance (i.e.) in the event of single temp tablespace gets corrupted, we can just remove that from the tablespace group and drop the same and create a new temp tablespace and assign the same in tablespace group.

• Huge temp waits for read and writes can be minimized.

• The I/O gets distributed when the having multiple tempfiles.

• For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.

Using Flash cache

As we all know EXADATA comes with inbuilt flash cache. I just did some search on how to achieve the same in non EXADATA boxes.

Starting from 11gR2 oracle introduced database smart flash cache which is available only on Linux and Solaris operating systems.

Using flash cache the size of buffer cache can be increased without adding more memory.

Until oracle 11gR1 , the buffers in buffer cache are overwritten using LRU list. so if the buffer is needed again then a physical read is performed (ie) the data blocks are fetched from disk and placed in the buffer cache. With flash cache, the buffers are written from buffer cache to flash cache where the buffers are not overwritten. So when these buffers are needed, the data blocks are fetched from flaSH cache directly.

To implement this we need to set two parameters in out init.ora file :

• db_flash_cache_file

• db_flash_cache_size

The db_flash_cache_file takes a value of a filename with its path. This file acts as memory space. The DB_FLASH_CACHE_FILE parameter can point to a raw device (including a logical volume), afile, or an ASM disk group

The parameter db_flash_cache_size is the size of flash cache.

Note : If we do not have the onboard flash_cache on host and we try to enable the flash cache, we get this error message:

ORA-00439: feature not enabled: Server Flash Cache

ORA-01078: failure in processing system parameters

. A typical sizing of the Database Smart Flash Cache is two to ten times the size of SGA memory buffers. Note that header information is stored in the SGA for each flash cache buffer - 100 bytes perbuffer in exclusive mode, 200 bytes per buffer in RAC mode - so the number of available SGA buffers is reduced as the Database Smart Flash Cache size increases, and the SGA size should be increased accordingly.

Running RAC database on flash cache will remove bandwidth saturation at the cache fusion layer as a source of contention. Database Smart Flash Cache is not shared across RAC nodes; it is private to each RAC instance. For that reason, each RAC instance must define its own local flash cache file path. When the flash cache is managed by ASM, a separate diskgroup is needed for the flash cache of each instance.

This feature is good for read mostly or read only databases so that we can get maximum performance benefits.

How to Enable Tracing for srvctl and asmcmd utilities

The following environmental variables needs to be set to have tracing enabled.

For using srvctl utility

export SRVM_TRACE=TRUE

Setting the above variable to true enbles tracing . Setting it to false disables (This is default).

For asmcmd utilty

export DBI_TRACE=1

setting this to 1 will enable tracing and a value of 0 will disable (This is default).