Oracle Database Administration

 resolve the reasons for the hung database How to Collect Diagnostics for Database Hanging Issues [ID 452358.1] use the command sqlplus -prelim "/ as sysdba" to log in with the -prelim option. execute the oradebug hanganalyze command to analyze a hung database SQL>oradebug hanganalyze 3 In RAC SQL> oradebug setinst all SQL> oradebug -g def hanganalyze 3 oradebug hanganalyze command a couple of times to generate dump files for varying process states. get a systemstate dump from a non-RAC system by executing the following set of commands. SQL> oradebug setmypid Statement processed. SQL> oradebug dump systemstate 266 Statement processed. SQL> Issue the following commands to get a systemstate dump in a RAC environment: SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug -g all dump systemstate 266 take the systemstate dumps a few times, You can also gather a hanganalyze trace file to identify hung sessions with SQL*Plus when connecte...

To verify this run the following query. It will show the size of the recovery area and how full it is: set lines 100 col name format a60 select name , floor(space_limit / 1024 / 1024) "Size MB" , ceil(space_used  / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name /   To fix the problem, you need to either make the flash recovery area larger, or remove some files from it . If you have the disk space available, make the recovery area larger:  alter system set db_recovery_file_dest_size=<size> scope=both / In RAC ALTER SYSTEM SET db_recovery_file_dest_size='6G' SCOPE=BOTH SID='*'; qlplus "/as sysdba" archive log list; !df -h or !df -kgt show parameter db_recovery; NAME                      TYPE         VALUE ----                      ----         ----- db_recovery_file_dest ...

Database Refresh using RMAN Duplicate command based on SCN find the SCN from PRD database   #!/usr/bin/ksh export ORAENV_ASK='NO' export ORACLE_SID=PRD /usr/local/bin/oraenv export NLS_LANG=American_america.us7ascii; export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"; $ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_TEST.log <<EOF connect auxiliary sys/PASSWORD@TEST ; run { allocate auxiliary channel aux1 device type disk; set until SCN 42612597059; duplicate target database to "TEST" pfile='/u01/app/xxxx/product/10.2.0/db_1/dbs/initTEST.ora' NOFILENAMECHECK; } EOF mailx -s "PRD_Tape to TEST duplication result" " hello@gmail.com.au " < /tmp/duplicate_tape_TEST.log Duplicate command based on data and time #!/usr/bin/ksh export ORAENV_ASK='NO' export ORACLE_SID=PRD . oraenv export NLS_LANG=American_america.us7ascii; export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"; $ORACLE_HOM...

TEMP Tablespace administration SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM   V$temp_space_header GROUP  BY tablespace_name; Free space in a temporary tablespace. select * from (select a.tablespace_name, sum(a.bytes/1024/1024) allocated_mb from dba_temp_files a where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x, (select sum(b.bytes_used/1024/1024) used_mb, sum(b.bytes_free/1024/1024) free_mb from v$temp_space_header b where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name); find out which SQL statement is using up space in a sort segment . select s.sid || ',' || s.serial# sid_serial, s.username, o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, o.sqladdr address, h.hash_value, h.sql_text from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t where o.session_addr = s.saddr and o.sqladdr = h.address (+) and o.tablespace = t.tablespace_name order by s.si...