Thursday, March 29, 2007

Redo Logfile Size Advisor

The Redo Logfile Size Advisor can be used to determine the least optimal online redo log file size based on the current FAST_START_MTTR_TARGET setting and MTTR statistics.Which means that Redo Logfile Size Advisor is enabled only if FAST_START_MTTR_TARGET is set.
The Advisor provides a recommended optimal smallest online redo log file size.we can then adjust the online redo log file size to the recommended optimal size ,eliminating the need to manually determine an optimal size.

To use the advisor we need to query the V$INSTANCE_RECOVERY VIEW.

If our redo log file size is under sized then the checkpoint process is driven by WRITES_LOGFILE_SIZE i.e. its driven by the smallest redo log file size.

This can be clarified by quering the V$INSTANCE_RECOVERY VIEW.

Lets say one of our Log file is too small and we have set the FAST_START_MTTR_TARGET=30

We have done some Transactions which has generated redo entries , now we would like to capture the minimum optimal size of the redo log file.

SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE,
OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE OPTIMAL_LOGFILE_SIZE
----------- -------------- ----------- -------------------

------------------
30 28 0 1022

32

Its clear from the above query that the chekpoint is driven by WRITES_LOGFILE_SIZE as described above and the advice is to have a Log file size of atleast 32MB Size.

Now if we recreate the log file with the size recommendation ie 32 MB and fire the same query again lets see what happens

SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE, OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;


TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE

OPTIMAL_LOGFILE_SIZE
----------- -------------- ----------- -------------------

--------------------
34 38 924 0

32

This shows that the checkpoint is now driven by WRITES_MTTR ie the FAST_START_MTTR parameter.

CAVEAT: An online redo log file’s size is considered optimal if it does not drive incremental checkpointing more aggressively than needed by FAST_START_MTTR_TARGET.

Tuesday, March 27, 2007

Rename an Oracle Database

Below Mentioned Steps will help you to rename an oracle Database

  • Start by making a full database backup of your database (in case you need to restore if this procedure is not working).
  • Execute this command from sqlplus while connected to 'SYS AS SYSDBA':

ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

  • Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql.
  • Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change "CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...".
  • Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run dbrename.sql.
  • Rename the database's global name:

ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;

What is inside X$tables in Oracle

The below mentioned list may not be complete or accurate, but represents an attempt to figure out what information they contain. I suggest not write queries against these tables as they are internal to Oracle, and Oracle may change them without any prior notification.

X$K2GTE2
Kernel 2 Phase Commit Global Transaction Entry Fixed Table
X$K2GTE
Kernel 2 Phase Commit Global Transaction Entry Fixed Table
X$BH
Buffer headers contain information describing the current contents of a piece of the buffer cache.
X$KCBCBH
Cache Buffer Current Buffer Header Fixed Table. It can predict the potential oss of decreasing the number of database buffers. The db_block_lru_statistics parameter has to be set to true to gather information in this table.
X$KCVFH
File Header Fixed Table
X$KDNCE
SGA Cache Entry Fixed Table
X$KDNST
Sequence Cache Statistics Fixed Table
X$KDXHS
Histogram structure Fixed Table
X$KDXST
Statistics collection Fixed Table
X$KGHLU
One-row summary of LRU statistics for the shared pool
X$KGLBODY
Derived from X$KGLOB (col kglhdnsp = 2)
X$KGLCLUSTER
Derived from X$KGLOB (col kglhdnsp = 5)
X$KGLINDEX
Derived from X$KGLOB (col kglhdnsp = 4)
X$KGLLC
Latch Clean-up state for library cache objects Fixed Table
X$KGLPN
Library cache pin Fixed Table
X$KGLTABLE
Derived from X$KGLOB (col kglhdnsp = 1)
X$KGLTR
Library Cache Translation Table entry Fixed Table
X$KGLTRIGGER
Derived from X$KGLOB (col kglhdnsp = 3)
X$KGLXS
Library Cache Access Table
X$KKMMD
Fixed table to look at what databases are mounted and their status
X$KKSBV
Cursor Cache Bind Variables
X$KSMSP
Each row represents a piece of memory in the shared pool
X$KSQDN
Global database name
X$KSQST
Enqueue statistics by type
X$KSUCF
Cost function for each Kernel Profile (join to X$KSUPL)
X$KSUPL
Resource Limit for each Kernel Profile
X$KSURU
Resource Usage for each Kernel Profile (join with X$KSUPL)
X$KSQST
Gets and waits for different types of enqueues
X$KTTVS
indicate tablespace that has valid save undo segments
X$KVII
Internal instance parameters set at instance initialization
X$KVIS
Oracle Data Block (size_t type) variables
X$KVIT
Instance internal flags, variables and parameters that can change during the life of an instance
X$KXFPCDS
Client Dequeue Statistics
X$KXFPCMS
Client Messages Statistics
X$KZDOS
Represent an os role as defined by the operating system
X$KZSRO
Security state Role: List of enabled roles
X$LE
Lock Element : each PCM lock that is used by the buffer cache (gc_db_locks)
X$MESSAGES
Displays all the different messages that can be sent to the Background processes
X$NLS_PARAMETERS
NLS database parameters

Monday, March 26, 2007

Make your Windows Faster & Smoother...

Disable unnecessary programs When you install some programs they will insert themselves into an area of your registry that will cause them to load when your computer starts up. Obviously this will use memory and slow down your system.

1. Start Regedit.

2. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\Curre ntVersion\ Run

3. Once there, locate any entry's on the right. You can identify the program by the path to the executable. Find programs that you can live without starting up from the list.

4. Right click on them and select Delete

5. You may also want to navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\Curre ntVersion\ Runonce and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Curr entVersion \Run.

6. Delete unnecessary programs.

7. Also go to START - PROGRAMS - STARTUP and see if there is anything there you wish to remove.

8. Reboot your computer.

Modify application timeout The operating system has a set amount of time that a program must be frozen for before it is timed out. Often this number is set too high, but in some circumstances it is set too low.

If the program is doing a lot of calculations in the background the computer may think that it is timed out. To prevent this, increase the value of the timeout in the registry.

1. Start Regedit. If you are unfamiliar with regedit please refer to our FAQ on how to get started.

2. Navigate to HKEY_CURRENT_USER\Control Panel\Desktop

3. Select HungAppTimeout from the list on the right.

4. Right on it and select Modify.

5. Change the value to the new timeout value.

6. Reboot your computer.

Open Outlook Express faster For some users when they remove windows messenger or just disable it from starting up automatically they experience a great slow down when launching Outlook express. Users who experience such a slow down will find a error in the system error log saying The server {FB7199AB-79BF-11D2-8D94-0000F875C541} did not register with DCOM within the required timeout. The solution to this problem is quite simple. Just Open up regedit and search for the string {FB7199AB-79BF-11D2-8D94-0000F875C541} and modify the keys InProcServer32 and LocalServer32 default key to a empty string.

Disable unnecessary services to free system resources

Services are programs that run when the computer starts up and continue to run as they aid the operating system in functionality. There are many services that load and are not needed which take up memory space and CPU time. Disabling these services will free up system resources which will speed up your overall computer experience. I recommend that you sort through the list and read the descriptions to decide if you need that service depending on what you want to do with your computer. Remember, you can always turn the service back on if you find that you need it in the future. Below is the procedure to turn off a service.

1. Click the start button.

2. Select run from the bottom of the right column.

3. Then type services.msc in the box and click ok.

4. Once the services window has loaded we are ready to turn off unneeded services.

5. For instructional purposes we are going to turn off the Portable Media Serial Number service.

6. Find this service in the list and select it with the mouse.

7. Right click and select Properties.

8. Once the properties windows has loaded locate the Start up type drop down box and select disable.

9. Then just click ok and the next time the computer starts the service will not be loaded.

System Settings for a Game Machine

Sunday, March 25, 2007

How to Find Over-all Database Size?

Big Portion of the database size comes from Datafiles.

To find out the size allocated to datafiles you can use below mentioned query :

1) select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all Temporary Files :

2) select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of Online Redo Log Files :

3) select sum(bytes)/1024/1024 "Meg" from sys.v_$log;


So counting all of them you will get the size of the Database.

How to find Up-Time of Database?

Execute the Below mentioned query to find out the uptime of the DB.

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"FROM sys.v_$instance;