A easy approach to identify this issue is to do a trace and from the trace file you can easily tell how much physical or consistent read Oracle has done for the query. If there is a lot of reads involved but the query returns 0 row, then it's clearly that Oracle does something unnecessary.
2012/07/31
Query empty table takes long time
If you encounter the issue where you simply query a table using 'select * from ' and it takes a long time only to return 0 row, you may have high water mark issue. In some table where at one point it had thousands of rows in it and then was cleaned up by using delete command, the high water mark stays. This means Oracle will scans all the blocks under high water mark in a full table scan, which could be amount of work. To resolve, truncate the table so Oracle does not have to scan all those blocks.
A easy approach to identify this issue is to do a trace and from the trace file you can easily tell how much physical or consistent read Oracle has done for the query. If there is a lot of reads involved but the query returns 0 row, then it's clearly that Oracle does something unnecessary.
A easy approach to identify this issue is to do a trace and from the trace file you can easily tell how much physical or consistent read Oracle has done for the query. If there is a lot of reads involved but the query returns 0 row, then it's clearly that Oracle does something unnecessary.
2011/04/13
How to Kill session in Oracle
The command 'alter system kill session' is not actually killing the target session (like kill -9 would do for OS processes). It just sets a bit in the target sessions state object, which marks that the target session should end. But its entirely up the target session to check this bit and act on it! Normally the target sessions are nice and check that bit often enough in their code, act on it and die. But sometimes when the target session happens to be busy looping in some tight loop (due a bug perhaps) or is hung, then it never gets to check that “please die” bit and never exits. This is why DBAs often need to kill the OS process or thread via OS tools to get rid of that session (and its locks, transactions) as when you kill the OS process, PMON will detect it (if not fast enough then it can be woken up via ORADEBUG WAKEUP call few times) and clean up after that session.
Steps to kill session in Oracle:
1. Use 'alter system kill session' to kill session.
2. If that doesn't work immediately then check whether the target session has acknowleged the kill and rolling back its transaction.
4. If couple of minutes after killing the process from OS level that session is still not released (no rollback), then attaching to the OS process with oradebug and run 'ORADEBUG WAKEUP 2' couple of times and check is the session is gone. The "2" means Oracle PID of PMON process which is usually 2, but you should check it from your v$process view.
Steps to kill session in Oracle:
1. Use 'alter system kill session' to kill session.
2. If that doesn't work immediately then check whether the target session has acknowleged the kill and rolling back its transaction.
SQL>select used_urec from v$transaction where addr = (select taddr from v$session where sid= and serial#=);
4. If couple of minutes after killing the process from OS level that session is still not released (no rollback), then attaching to the OS process with oradebug and run 'ORADEBUG WAKEUP 2' couple of times and check is the session is gone. The "2" means Oracle PID of PMON process which is usually 2, but you should check it from your v$process view.
SQL>oradebug setospid ;
SQL>oradebug wakeup 2;
SQL>oradebug setospid ;
SQL>oradebug event immediate crash;
2011/04/08
Logging Attributes in Create Table Statement
Tables created with logging or nologging clause will stay in that way. If the logging clause is not used when creating table (other than LOBs), it defaults to the logging attribute of the tablespace in which it resides.
With respect to the nologging option, three benefits listed in the Administrator's Guide are:
The logging clause lets you specify whether creation of a database object will be logged in the redo log file (LOGGING) or not (NOLOGGING). If you create a table with NOLOGGING, but cannot afford to lose the data (which implicitly means you need the table it is stored in), the first step after the data load is complete is to take a backup.
The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.
With respect to the nologging option, three benefits listed in the Administrator's Guide are:
- Space is saved in the redo log files
- The time it takes to create the table is decreased
- Performance improves for parallel creation of large tables
The logging clause lets you specify whether creation of a database object will be logged in the redo log file (LOGGING) or not (NOLOGGING). If you create a table with NOLOGGING, but cannot afford to lose the data (which implicitly means you need the table it is stored in), the first step after the data load is complete is to take a backup.
The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.
2011/02/04
Why (on Linux) am I seeing so much RAM usage?
This is a guest post by Dayid http://dayid.org
There are better options to see your memory usage; however it seems `free` is more attune to creating the confusion I’m attempting to quell here. That said, see the Redhat docs about /proc/meminfo
Other commands to use to see memory usage
There’s no reason to clear what’s in RAM until you need more space to write to it.
The short answer analogy
Buffers and cache in RAM being cleared is silly. Imagine a professor, who rather than writing all the way across the chalkboard, finishes a sentence and immediately erases and starts writing in the upper left corner AGAIN and AGAIN and AGAIN.
OR imagine you like a song. You record it to the beginning of a cassette tape. When you want a new song, do you re-record over the first song or record after it?
AKA: The horrible House/Barn analogy
Many people new to Linux or computers in general have a poor understanding of how RAM works. On Linux systems, most users will look at `top` or use `free` to see the amount of memory installed and/or free. Below is an example:
We could take a trip to a million places for this horrible analogy, but let’s pretend we’re on a country farm.
Rather than working with 2024MB of RAM and 1953MB of SWAP, we’ll say we’ve got 20 beds in the house, and 20 beds in the barn.
Rather than programs we’ll have people occupying the space.
For our purposes, ignore costs of cleaning the bedding, water, etc.
The house can hold active workers or non-active workers.
Due to its distance and the time to get to/from it, the barn can only hold non-active workers. When a worker is called from the barn they will have to pass through the house and stay in the house while they work.
10 laborers show up to a job. Since the house is closer to the food, showers, and work they’ll be doing, we let them stay in the house.
10 of our 20 beds are used by active workers.
Our farm in `free -m`:
18 of our 20 beds are used by active workers.
Our farm in `free -m`:
18 of 20 beds are used. 8 by active workers, 10 by non-active workers.
Our farm in `free -m`:
We have a new job on the farm, so we have 4 new people show up. We do not have enough beds for them. 2 of the 10 who are not active leave. We move in those 4 new people.
20 of 20 beds are used. 12 by active workers, 8 by non-active workers.
Our farm:
It’s production season and we have a lot to do around the farm. We setup another program and need to hire 14 new workers for it. We’ll have to kick out the 8 non-active workers and move in 8 of the new workers. However, because we run out of rooms in the house, our least important workers will have to stay in the barn. The barn is still good storing area, but it will take them longer to get to and from the job each time they are required to.
20 of 20 beds are used by active workers. 6 rooms in the barn are used.
Our farm:
Our farm:
There are better options to see your memory usage; however it seems `free` is more attune to creating the confusion I’m attempting to quell here. That said, see the Redhat docs about /proc/meminfo
Other commands to use to see memory usage
$ vmstat -aS M #see the "inactive" column for a rough "free" idea.
There’s no reason to clear what’s in RAM until you need more space to write to it.
The short answer analogy
Buffers and cache in RAM being cleared is silly. Imagine a professor, who rather than writing all the way across the chalkboard, finishes a sentence and immediately erases and starts writing in the upper left corner AGAIN and AGAIN and AGAIN.
OR imagine you like a song. You record it to the beginning of a cassette tape. When you want a new song, do you re-record over the first song or record after it?
AKA: The horrible House/Barn analogy
Many people new to Linux or computers in general have a poor understanding of how RAM works. On Linux systems, most users will look at `top` or use `free` to see the amount of memory installed and/or free. Below is an example:
dayid@emiline ~ $ free -m
total used free shared buffers cached
Mem: 2024 1970 53 0 19 1669
-/+ buffers/cache: 281 1742
Swap: 1953 4 1948
We could take a trip to a million places for this horrible analogy, but let’s pretend we’re on a country farm.
Rather than working with 2024MB of RAM and 1953MB of SWAP, we’ll say we’ve got 20 beds in the house, and 20 beds in the barn.
Rather than programs we’ll have people occupying the space.
For our purposes, ignore costs of cleaning the bedding, water, etc.
The house can hold active workers or non-active workers.
Due to its distance and the time to get to/from it, the barn can only hold non-active workers. When a worker is called from the barn they will have to pass through the house and stay in the house while they work.
10 laborers show up to a job. Since the house is closer to the food, showers, and work they’ll be doing, we let them stay in the house.
10 of our 20 beds are used by active workers.
Our farm in `free -m`:
total used free shared buffers cached
House: 20 10 10 0 0 0
-/+ buffers/cache: 10 10
Barn: 20 0 20
18 of our 20 beds are used by active workers.
Our farm in `free -m`:
total used free shared buffers cached
House: 20 18 2 0 0 0
-/+ buffers/cache: 18 2
Barn: 20 0 20
18 of 20 beds are used. 8 by active workers, 10 by non-active workers.
Our farm in `free -m`:
total used free shared buffers cached
House: 20 18 2 0 0 10
-/+ buffers/cache: 8 12
Barn: 20 0 20
We have a new job on the farm, so we have 4 new people show up. We do not have enough beds for them. 2 of the 10 who are not active leave. We move in those 4 new people.
20 of 20 beds are used. 12 by active workers, 8 by non-active workers.
Our farm:
total used free shared buffers cached
House: 20 20 0 0 0 8
-/+ buffers/cache: 12 8
Barn: 20 0 20
It’s production season and we have a lot to do around the farm. We setup another program and need to hire 14 new workers for it. We’ll have to kick out the 8 non-active workers and move in 8 of the new workers. However, because we run out of rooms in the house, our least important workers will have to stay in the barn. The barn is still good storing area, but it will take them longer to get to and from the job each time they are required to.
20 of 20 beds are used by active workers. 6 rooms in the barn are used.
Our farm:
total used free shared buffers cached
House: 20 20 0 0 0 0
-/+ buffers/cache: 20 0
Barn: 20 6 14
Our farm:
total used free shared buffers cached
House: 20 20 0 0 0 16
-/+ buffers/cache: 4 16
Barn: 20 6 14
2011/01/06
Resolving Shutdown Immediate Hang Situations
Copied from here:
http://askdba.org/weblog/2008/05/shutdown-immediate-hang-2/
Shutdown immediate can take long time to complete (appear to be hung) because of three reasons:
1. Uncommitted transactions are being rolled back.
2. SMON is cleaning temp segments or performing delayed block cleanouts.
3. Processes still continue to be connected to the database and do not terminate.
1. Uncommitted transactions are being rolled back:
This is the case when the message ‘Waiting for smon to disable tx recovery’ is posted in the alert log after we issue shutdown immediate.
There are two reasons for this:
- A large query was running at the time of shutdown immediate.
-A large transaction was running at the time of shutdown immediate.
For large queries:
For large transactions:
If the large transaction is aborted and then shutdown is issued then we have to query v$fast_start_transactions and v$fast_start_server, we will not see anything in v$transaction at this time.
At this particular moment transaction recovery is going on and the count(*) will keep on decreasing:
But it is not desirable under some circumstances such as, when we have very short maintance window and we need to perform a shutdown immediate to do some work, in those cases we can use the following event and set in the init.ora file TEMPERORARLY To disable transaction recovery:
2. SMON is cleaning temp segments or performing delayed block cleanouts:
During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON cleans up extents which are no longer needed and marking them as freed. It means that count from uet$ will decrease and count in fet$ will increase.
To verify that the temporary segments are decreasing have an active session available in SQL during the SHUTDOWN IMMEDIATE. Run query to ensure the database is not hanging, but is actually perform extent cleanup:
3. Processes still continue to be connected to the database and do not terminate:
After issuing shutdown immediate, If we see entries in alert log file as:
1. Before shutdown immediate, shutdown the listener:
3 Manually kill them as:
Do not forget to bring up the listener after startup
In addition to this you can set 10046 event in the session used to shutdown the instance. This will help to tell the event on which session is waiting
http://askdba.org/weblog/2008/05/shutdown-immediate-hang-2/
Shutdown immediate can take long time to complete (appear to be hung) because of three reasons:
1. Uncommitted transactions are being rolled back.
2. SMON is cleaning temp segments or performing delayed block cleanouts.
3. Processes still continue to be connected to the database and do not terminate.
1. Uncommitted transactions are being rolled back:
This is the case when the message ‘Waiting for smon to disable tx recovery’ is posted in the alert log after we issue shutdown immediate.
There are two reasons for this:
- A large query was running at the time of shutdown immediate.
-A large transaction was running at the time of shutdown immediate.
For large queries:
SQL > select count(*) from v$session_longops where time_remaining>0;
For large transactions:
SQL > select sum(used_ublk) from v$transaction;
If the large transaction is aborted and then shutdown is issued then we have to query v$fast_start_transactions and v$fast_start_server, we will not see anything in v$transaction at this time.
At this particular moment transaction recovery is going on and the count(*) will keep on decreasing:
SQL > select count(*) from v$fast_start_transaction;
But it is not desirable under some circumstances such as, when we have very short maintance window and we need to perform a shutdown immediate to do some work, in those cases we can use the following event and set in the init.ora file TEMPERORARLY To disable transaction recovery:
event=”10513 trace name context forever, level 2″
2. SMON is cleaning temp segments or performing delayed block cleanouts:
During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON cleans up extents which are no longer needed and marking them as freed. It means that count from uet$ will decrease and count in fet$ will increase.
To verify that the temporary segments are decreasing have an active session available in SQL during the SHUTDOWN IMMEDIATE. Run query to ensure the database is not hanging, but is actually perform extent cleanup:
SQL> select count(block#) from fet$;
COUNT(BLOCK)
----------
115
SQL> select count(block#) from uet$;
COUNT(BLOCK)
----------
713
SQL> select count(block#) from fet$;
COUNT(BLOCK)
----------
210
SQL > select count(block#) from uet$;
COUNT(BLOCK)
----------
512
event=”10061 trace name context forever, level 10″
3. Processes still continue to be connected to the database and do not terminate:
After issuing shutdown immediate, If we see entries in alert log file as:
Tue Jan 8 12:00:27 2008
Active call for process 10071 user 'oracle' program 'oracle@server.domain.abc (J001)'
SHUTDOWN: waiting for active calls to complete.
Tue Jan 8 12:00:57 2008
SHUTDOWN: Active sessions prevent database close operation
1. Before shutdown immediate, shutdown the listener:
$ lsnrctl stop
$ ps -eaf | grep LOCAL
3 Manually kill them as:
# Kill -9
Do not forget to bring up the listener after startup
In addition to this you can set 10046 event in the session used to shutdown the instance. This will help to tell the event on which session is waiting
SQL>alter session set events '10046 trace name context forever, level 12'
SQL>Shutdown immediate;
2010/09/17
How to Kill RMAN Backup Sessions
You can identify the Oracle session ID for an RMAN channel by looking in the RMAN log for messages with the format shown in the following example:
channel ch1: sid=15 devtype=SBT_TAPE
2010/08/31
ORA-16038: log <string> sequence# <string> can not be archived
This error can show up when trying to archive an active logfile but the logfile is unavailable.
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 1 sequence# 19 cannot be archived
ORA-00312: online log 1 thread 1: '/orafs04/oradata/rmant01/redo01a.log'
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance rmant01 (thread 1)
ORA-00312: online log 1 thread 1: '/orafs04/oradata/rmant01/redo01a.log'
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile ('/orafs04/oradata/rmant01/redo01a.log') size 100M;
Database altered.
2010/02/18
How to Format a Hard Drive in Linux
Linux referes to hard drives as either 'hdx' or 'sdx' where x is a letter, starting with a, which represents the order in which the drive was added to or detected by the compouter. The 'hd' prefix is used for IDE and PATA (formerly just ATA), and the 'sd' prefix is used for SCSI, SATA and USB drives. Usually a number is also put at the end of 'hdx' or 'sdx' to denote different partitions on the same physical drive, but for the purpose of formatting you only need to know which letter the drive you want to format is.
You can see all the drives attached to your system by typing the command 'ls /dev/hd*' and 'ls /dev/sd*' depending on which type the drives are.
First you will use the 'fdisk /dev/' command to erase any old partitions on the drive and create a new one.
Then, you will use the 'mkfs -t /dev/' to create the filesystem on the drive.
Finally, you will edit /etc/fstab file to mount the drive whenever system is rebooted.
You can see all the drives attached to your system by typing the command 'ls /dev/hd*' and 'ls /dev/sd*' depending on which type the drives are.
First you will use the 'fdisk /dev/
Then, you will use the 'mkfs -t
Finally, you will edit /etc/fstab file to mount the drive whenever system is rebooted.
2009/12/22
Rebuild Table as Partitioned Table
1. Check if tablespace has enough room for an extra copy of the table.
2. Drop any PK, Indexes, FK for the table.
3. Rename the table to a temporary table name.
4. Create new partitioned table with neccessary contraints.
5. Merge data from temporary table into the partitioned table.
6. Drop the partitioned table.
2. Drop any PK, Indexes, FK for the table.
3. Rename the table to a temporary table name.
4. Create new partitioned table with neccessary contraints.
5. Merge data from temporary table into the partitioned table.
6. Drop the partitioned table.
2009/12/02
Cronjob Doesn't Source Profile By Default
The script runs perfectly in command line won't necessarily run in cronjob. The most possible reason for this is that 'PATH' is not set in the script because by default cronjob won't source '.profile'. And user won't notice this because if user runs the script in command line, he's already sourced his profile. You should include it in your crontab shells or run commands in a way they source profile.
2009/09/23
Warning: no filemap entries available
The following warning occurs while doing opatch lsinventory:
"Warning: no filemap entries available"
Cause:
$ORACLE_HOME/inventory/oneoffs//etc/config/actios or $ORACLE_HOME/inventory/oneoffs//etc/config/inventory does not exist.
Solution:
1. Download the failed patch.
2. Unzip the downloaded patch.
3. cd/etc/config
4. cp inventory $ORACLE_HOME/inventory/oneoffs/<patch number>/etc/config
5. cp actions $ORACLE_HOME/inventory/oneoffs/<patch number>/etc/config
6. Run 'opatch lsinventory' again to verify the result.
Please note this is to fix an issue where "opatch lsinventory" lists an already installed patch this way
"Warning: no filemap entries available"
Cause:
$ORACLE_HOME/inventory/oneoffs/
Solution:
1. Download the failed patch.
2. Unzip the downloaded patch.
3. cd
4. cp inventory $ORACLE_HOME/inventory/oneoffs/<patch number>/etc/config
5. cp actions $ORACLE_HOME/inventory/oneoffs/<patch number>/etc/config
6. Run 'opatch lsinventory' again to verify the result.
Please note this is to fix an issue where "opatch lsinventory" lists an already installed patch this way
2009/09/16
Loader Throughput Too High for Grid Control
Grid Control 10.2.0.4
Metric 'Loader Throughput(rows per second)' is alerted to exceed the critical threshold (3000) on Grid Control homepage. To solve this problem, shutdown grid control and set em.loader.threadPoolSize parameter in file 'emoms.properties' to a higher value (1 to 10). The file is located in $OMS_HOME/sysman/config/ directory. Then, restart Grid Control.
Metric 'Loader Throughput(rows per second)' is alerted to exceed the critical threshold (3000) on Grid Control homepage. To solve this problem, shutdown grid control and set em.loader.threadPoolSize parameter in file 'emoms.properties' to a higher value (1 to 10). The file is located in $OMS_HOME/sysman/config/ directory. Then, restart Grid Control.
2009/09/11
Recycle the listener.log File
The parameter 'LOGGING_LISTENER' in listener.ora file controls whether listener will be logged, and it defaults to LOGGING_LISTENER=ON. To turn off the log, you have to add LOGGING_LISTENER=OFF into listener.ora file.
If you try to rename or remove the listener.log file without shutting down listener on Windows platform, you will notice that Windows holds a lock on this file and returns an error. Even under Unix, the Oracle listener process holds an open handle to the file. You can remove the file, but Oracle will not re-create the file when it attempts to write it again.
Here is a solution for renaming or removing the listener.log file without having to stop and restart the listener:
lsnrctl set log_status off
mv listener.log listener.log.old
touch listener.log
lsnrctl set log_status on
If you try to rename or remove the listener.log file without shutting down listener on Windows platform, you will notice that Windows holds a lock on this file and returns an error. Even under Unix, the Oracle listener process holds an open handle to the file. You can remove the file, but Oracle will not re-create the file when it attempts to write it again.
Here is a solution for renaming or removing the listener.log file without having to stop and restart the listener:
lsnrctl set log_status off
mv listener.log listener.log.old
touch listener.log
lsnrctl set log_status on
2009/08/19
Estimate size for undo tablespace
Sizing an UNDO tablespace requires three pieces of information:
UR: UNDO_RETENTION in seconds
UPS: Number of undo data blocks generated per second
DBS: Overhead varies based on extent and file size (db_block_size)
UndoSpace = (UR * (UPS * DBS) + DBS)
or, when the guesstime equates to zero, then add a multiplier (24) to the overhead (DBS) to derive more appropriate results:
UndoSpace = (UR * (UPS * DBS)) + (DBS * 24)
UNDO_RETENTION and DB_BLOCK_SIZE can be obtained from the initialization file. UPS can be acquired from the following query:
SELECT (sum(undoblks))/sum((end_time-begin_time)*86400)
FROM v$undostat;
UR: UNDO_RETENTION in seconds
UPS: Number of undo data blocks generated per second
DBS: Overhead varies based on extent and file size (db_block_size)
UndoSpace = (UR * (UPS * DBS) + DBS)
or, when the guesstime equates to zero, then add a multiplier (24) to the overhead (DBS) to derive more appropriate results:
UndoSpace = (UR * (UPS * DBS)) + (DBS * 24)
UNDO_RETENTION and DB_BLOCK_SIZE can be obtained from the initialization file. UPS can be acquired from the following query:
SELECT (sum(undoblks))/sum((end_time-begin_time)*86400)
FROM v$undostat;
2009/08/17
How to Shutdown and Start Grid Control and All Its Components
Grid Control: 10.2.0.4
Shutdown Grid Control
1. $OMS_HOME/bin/emctl stop oms
2. $OMS_HOME/bin/emctl stop iasconsole
3. $OMS_HOME/opmn/bin/opmnctl stopall
4. $AGENT_HOME/bin/emctl stop agent
5. shutdown repository database
6. shutdown listener
Start Grid Control
1. start listener
2. start repository database
3. $OMS_HOME/bin/emctl start oms
4. $OMS_HOME/opmn/bin/opmnctl startproc ias-component=WebCache
5. $AGENT_HOME/bin/emctl start agent
6. $OMS_HOME/bin/emctl start iasconsole
Shutdown Grid Control
1. $OMS_HOME/bin/emctl stop oms
2. $OMS_HOME/bin/emctl stop iasconsole
3. $OMS_HOME/opmn/bin/opmnctl stopall
4. $AGENT_HOME/bin/emctl stop agent
5. shutdown repository database
6. shutdown listener
Start Grid Control
1. start listener
2. start repository database
3. $OMS_HOME/bin/emctl start oms
4. $OMS_HOME/opmn/bin/opmnctl startproc ias-component=WebCache
5. $AGENT_HOME/bin/emctl start agent
6. $OMS_HOME/bin/emctl start iasconsole
2009/08/11
Oracle Components Enabled
Oracle 10.2.0.2
You can check what components, like JVM or XML DB, are loaded into your Oracle Server by query view DBA_REGISTRY:
You can check what components, like JVM or XML DB, are loaded into your Oracle Server by query view DBA_REGISTRY:
SQL> SELECT comp_name, status FROM dba_registry;
2009/08/04
Enable archivelog mode in RAC
In 10g Release 1, the database must be mounted (not open) by an exclusive instance. In other words, set the CLUSTER_DATABASE parameter to FALSE. After executing the ALTER DATABASE SQL statement to change the archive log mode, shutdown the instance and restart it with the CLUSTER_DATABASE parameter set to TRUE before you restart the other instances.
1. Login to one of the nodes, disable the cluster instance parameter and set log archive destination:
1. Login to one of the nodes, set log archive destination:
1. Login to one of the nodes, disable the cluster instance parameter and set log archive destination:
$ sqlplus / as sysdba
SQL> alter system set cluster_database=false scope=spfile sid='';
SQL> alter system set log_archive_dest_1='' scope=spfile sid='*';
$ srvctl stop database -d
$ sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter system set cluster_database=true scope=spfile sid='';
SQL> shutdown immediate;
$ srvctl start database -d
1. Login to one of the nodes, set log archive destination:
$ sqlplus / as sysdba
SQL> alter system set log_archive_dest_1='' scope=spfile sid='*';
$ srvctl stop database -d
$ sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> shutdown immediate;
$ srvctl start database -d
2009/07/21
Memory Tuning Tips
Oracle 10gR2
You can check the current size of components in sga using v$ views like:
V$SGA
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS
If you enabled ASMM, memory statistics will be calculated and indication of memory tuning can be found in the following v$ views:
V$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAM_POOL_ADVICE
Another interactive way to tune memory is through 'Memory Advisor' interface which can be found in 'Advisor Central' from Grid Control.
SGA_TARGET vs. SGA_MAX_SIZE
SGA_MAX_SIZE is static parameter, while SGA_TARGET is dynamic. The size of SGA_MAX_SIZE is allocated from system memory after system start. On some platforms, the difference (SGA_MAX_SIZE-SGA_TARGET) is in virtual memory, which onlys taks space from disk. While on other platforms (Windows and Linux), it takes memory from system, which means much more SGA_MAX_SIZE than SGA_TARGET can be resouce waste on production system.
You can check the current size of components in sga using v$ views like:
V$SGA
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS
If you enabled ASMM, memory statistics will be calculated and indication of memory tuning can be found in the following v$ views:
V$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAM_POOL_ADVICE
Another interactive way to tune memory is through 'Memory Advisor' interface which can be found in 'Advisor Central' from Grid Control.
SGA_TARGET vs. SGA_MAX_SIZE
SGA_MAX_SIZE is static parameter, while SGA_TARGET is dynamic. The size of SGA_MAX_SIZE is allocated from system memory after system start. On some platforms, the difference (SGA_MAX_SIZE-SGA_TARGET) is in virtual memory, which onlys taks space from disk. While on other platforms (Windows and Linux), it takes memory from system, which means much more SGA_MAX_SIZE than SGA_TARGET can be resouce waste on production system.
2009/06/25
ORA-04031 and SGA settings
Oracle 10.2.0.2
OS RHEL 4
Recently, I got error message from Grid Control for one of our production databases.
Failed to connect to database instance: ORA-04031: unable to allocate 4120 bytes of shared memory ("large pool","unknown object","session heap","kzctxhugi1") (DBD ERROR: OCISessionBegin).
The message is pretty obvious: the large pool is not large enough for new allocations. Since it's 10g and we are using ASMM, the SGA is self-adjusted. We put 1.5G to sga_target and didn't specify a value for large_pool_size. Oracle suggests give a minimum value to large_pool_size so that Oracle won't squeeze large pool too small.
Another question I have during the research is that what's point of having both sga_target and sga_max_size.
Well, since sga_max_size is static and sga_target is dynamic, having set a larger value for sga_max_size than sga_target gives you a tuning margin for sga_target later without restarting the database. One thing we should pay attention is that on some OS, such as Windows and Linux, memory is allocated the same size as sga_max_size when the instance is started, but on some other OS, such as Sun Solaris, memory is allocated the same size as sga_target, and the other free memory (sga_max_size - sga_target) is ready for other use.
OS RHEL 4
Recently, I got error message from Grid Control for one of our production databases.
Failed to connect to database instance: ORA-04031: unable to allocate 4120 bytes of shared memory ("large pool","unknown object","session heap","kzctxhugi1") (DBD ERROR: OCISessionBegin).
The message is pretty obvious: the large pool is not large enough for new allocations. Since it's 10g and we are using ASMM, the SGA is self-adjusted. We put 1.5G to sga_target and didn't specify a value for large_pool_size. Oracle suggests give a minimum value to large_pool_size so that Oracle won't squeeze large pool too small.
Another question I have during the research is that what's point of having both sga_target and sga_max_size.
Well, since sga_max_size is static and sga_target is dynamic, having set a larger value for sga_max_size than sga_target gives you a tuning margin for sga_target later without restarting the database. One thing we should pay attention is that on some OS, such as Windows and Linux, memory is allocated the same size as sga_max_size when the instance is started, but on some other OS, such as Sun Solaris, memory is allocated the same size as sga_target, and the other free memory (sga_max_size - sga_target) is ready for other use.
2009/06/05
Grid Control Agent Crash with 'too many open files' Error
Grid Control Agent 10.2.0.4
Database: 10.2.0.2
OS Platform: RHEL Release 4 64bit
Agent crashes a lot, intermittently with 'too many open files' error inside Grid Control. Trace file 'emagent.trc' gives 'health check' error lik this:
2008-03-24 15:24:52 Thread-4124650400 ERROR fetchlets.healthCheck: GIM-00105: file not found
2008-03-24 15:24:52 Thread-4124650400 ERROR engine: [oracle_database,,health_check] : nmeegd_GetMetricData failed : Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above.
2008-03-24 15:24:52 Thread-4124650400 WARN collector: Error exit. Error message:Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above
Cause: Bug 5872000 - Healthcheck Error Occurs fror 32Bit Database on 64Bit OS Due to Bug4526916 Fix.
The Healthcheck file, namely $ORACLE_HOME/dbs/hc_.cat file differs in size from the memory structure used by the Agent to read it. This file is created by the database on startup time, if not present.
This happens when the database is e.g. 10.2.0.4 and the agent is 10.2.0.3 and vice versa.
Possible solutions:
1. Apply Patch 5872000 to databases on 64-bit machine.
This needs to be applied on top of 10.1 -> 10.2.0.3, and 11.1.0.6 databases. THe file $ORACLE_HOME/dbs/hc_.dat may need to be removed before starting up the database after patch application. This file is created on database start up if not present. The agent uses this file for the Healthcheck metric. By recreating the file on start up after the patch application, the file is the correct one needed by the agent.
2.Disable the healthcheck metric per database in Grid Control.
Check 379423.1 in Metalink on 'How to edit or disable the Health Check Metric Collection in Grid Control 10.2'.
Note: If the second workaround is applied, then you have to redo it everytime you add new database target into Grid Control.
Reference Doc in Metalink:
564617.1
566607.1
379423.1
469227.1
Database: 10.2.0.2
OS Platform: RHEL Release 4 64bit
Agent crashes a lot, intermittently with 'too many open files' error inside Grid Control. Trace file 'emagent.trc' gives 'health check' error lik this:
2008-03-24 15:24:52 Thread-4124650400 ERROR fetchlets.healthCheck: GIM-00105: file not found
2008-03-24 15:24:52 Thread-4124650400 ERROR engine: [oracle_database,
2008-03-24 15:24:52 Thread-4124650400 WARN collector:
Cause: Bug 5872000 - Healthcheck Error Occurs fror 32Bit Database on 64Bit OS Due to Bug4526916 Fix.
The Healthcheck file, namely $ORACLE_HOME/dbs/hc_.cat file differs in size from the memory structure used by the Agent to read it. This file is created by the database on startup time, if not present.
This happens when the database is e.g. 10.2.0.4 and the agent is 10.2.0.3 and vice versa.
Possible solutions:
1. Apply Patch 5872000 to databases on 64-bit machine.
This needs to be applied on top of 10.1 -> 10.2.0.3, and 11.1.0.6 databases. THe file $ORACLE_HOME/dbs/hc_.dat may need to be removed before starting up the database after patch application. This file is created on database start up if not present. The agent uses this file for the Healthcheck metric. By recreating the file on start up after the patch application, the file is the correct one needed by the agent.
2.Disable the healthcheck metric per database in Grid Control.
Check 379423.1 in Metalink on 'How to edit or disable the Health Check Metric Collection in Grid Control 10.2'.
Note: If the second workaround is applied, then you have to redo it everytime you add new database target into Grid Control.
Reference Doc in Metalink:
564617.1
566607.1
379423.1
469227.1
Subscribe to:
Posts (Atom)