Posts

Generate PGA Breakdown

Oracle Database allocates a program global area (PGA) for each server process. The PGA is used to process SQL statements and to hold logon and other session information. For the purposes of memory management, the collection of all PGAs is known as the instance PGA. Using an initialization parameter, you set the size of the instance PGA, and the database distributes memory to individual PGAs as needed. The content of the PGA memory varies, depending on whether or not the instance is running the shared server option. Generally speaking, the PGA memory is divided into the following areas: - Session Memory - Private SQL Area Session Memory Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private. Private SQL Area The private SQL area contains data such as bind variable values, query execution state information, and query e...
1 comment
Read more

Recover Standby database from ORA-00332: archived log is too small

When a standby database crashes and upon instance recovery gets stuck with error ORA-00332: archived log is too small - may be incompletely archived, then following set of actions must be carried out to restore the service. 1. Check that the instance is in MOUNTED. (select status from v$instance) 2. If not mounted, then start the database in mounted mode SQL> startup mount; 3. Search the alert log to identify the archive log that is corrupt,  Sample lines from the alert log Errors in file /u01/app/oracle/diag/rdbms/fxfgsby/FXFGSBY1/trace/FXFGSBY1_ora_19463.trc: ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '+FLASH/fxfgsby/archivelog/2013_08_19/thread_1_seq_173831.5998.823855747' Shutting down recovery slaves due to error 332 4. In the above example, the corrupt log file belongs to Thread#=1 and Sequence#=173831 5. Log on to the Primary database cluster and take a copy of the file from ASM onto the local file system. Not...
Post a Comment
Read more

Collect Diagnostic Data during a database hang.

The following set of diagnostic data collection routines must be run when database is not responding to TNS connection request and queries to views like v$session, etc hang. The symptoms of this hang are Unable to connect using tnsnames to local instance. E.g. if ssh on node 01A, then cannot sqlplus to instance hosted on that server. E.g. sqlplus username/password@connectstring Unable to connect using tnsname .e.g sqlplus username/password@connectstring_active Bequeath connections are successful. E.g. sqlplus / as sysdba Querying certain V$ views causes the session to hang. Diagnostic Data set 1:   SQL> set lines 250 pages 1000   SQL> spool xle_xkclfx.out   SQL> select * from x$le;   SQL> select * from x$kclfx;   SQL> exec dbms_lock.sleep(60);   SQL> select * from x$le;   SQL> select * from x$kclfx;   SQL> exec dbms_lock.sleep(60);   SQL> select * from x$le;   SQL> select ...
Post a Comment
Read more

Deconstruct Streams LCR

At times while resolving streams replication issues, it might be faster to generate individual queries associated with an failed LCR instead of relying on routines provided by the DBMS_APPLY_ADM package.In addition the script attached to this blog can be used to clear LCR back log in parallel as long as the source commit order is maintained. The driving query in the anonymous block is as follows and chose an appropriate filter to handle your issue for currec in (  select local_transaction_id, message_count  from dba_apply_error    where  -- local_transaction_id = '70.18.2231436' --# Use this filter to handle one LCR at a time     -- ERROR_MESSAGE LIKE '%DBOR_USERS%' --# Use this filter to handle LCR which have errors in the same table     -- error_creation_time >= to_timestamp('18-MAR-2013 08:00:00','dd-MON-RRRR HH24:MI:SS')  --#  Use the following two to filter on a date range.     --and error_creati...
Post a Comment
Read more

Oracle Dump commands

If you have generate oracle memory dump, the following command options can be used Dump Error Stack oradebug unlimit oradebug tracefile_name oradebug dump errorstack 3 Global Hanganalyze and Systemstates sqlplus "/ as sysdba" oradebug setmypid oradebug tracefile_name oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 266 oradebug dump errorstack 3
Post a Comment
Read more

Fetch Streams LCR payload

When a Streams apply process hits an error processing a transaction, the details of it are stored in the DBA_APPLY_ERROR table.  The key columns to look out for apart from the ERROR_MESSAGE is the local_transaction_id, MESSAGE_COUNT and MESSAGE_NUMBER . The column MESSAGE_COUNT provides the total number of transaction contained in each LCR and the MESSAGE_NUMBER column indicate which one of those transactions failed. Click on the link  Fetch LCR Payload    to download the .sql file  Executing the attached sql produces the DML output which Streams would have run has it not run into a issue.  Note: This DML produced by the script is not suitable for LOB columns
Post a Comment
Read more