The below SQL generates a SQL script that can be used to clone a database, putting tablespaces into hot backup mode one at a time. Run it on the source database, and edit the output to specify new target file and directory names, and change cp to rcp or scp if cloning to another server. […]
Read More Clone a databaseLists slow SQL in library cache for tuning investigations: set pages 9999 spool c.lst select elapsed_time/1000000 secs, executions, elapsed_time/1000000/greatest(executions,1) secs_per_exec, sql_text from v$sql where executions > 50 and elapsed_time/1000000/greatest(executions,1) > 1 order by 3 desc / spool off ed c.lst For older versions of oracle, use buffer_gets or disk_reads in place of elapsed_time.
Read More Slow SQL reportUpdate 13-Feb-07: Setting the datapump table_exists_action to replace does not overwrite views, sequences, plsql objects. They have to be dropped separately before datapump is called, see gotchas and code below. Update 02-Mar-07: dba_ views rather than all_ views have to be used to identify what objects to drop prior to datapump, for reasons explained in […]
Read More Using 10g datapump and scheduler to copy schemasSuspect too much auditing is on on a database? This script will show you what auditing is on: set pages 9999 select * from DBA_STMT_AUDIT_OPTS ; select * from DBA_PRIV_AUDIT_OPTS ; set termout off lines 192 spool 1.tmp select * from DBA_OBJ_AUDIT_OPTS ; spool off set termout on lines 80 host egrep ‘(S/|A/|/S|/A)’ 1.tmp Note […]
Read More Identify what auditing is onUsing the below memory listing script, I found two databases with large PGAs. That was strange since pga_aggregate_target was unset, sort_area_size was just 64k, and there were very few sessions. Checking PGA at session level showed the QMN sessions had high memory usage (over 200mb each). Which pointed to bug 2227360 “QMN process leaking memory” […]
Read More Pre 9202/9014: Advanced queuing runs away with server memory.The sm$ views are an easy way of seeing tablespace space usage. There is also an sm$ts_free view. set pages 9999 col tot_mb form 999,999 col use_mb form 999,999 col pct_used form 999 select t.tablespace_name, t.bytes/1024/1024 tot_mb, u.bytes/1024/1024 use_mb, 100*u.bytes/t.bytes pct_used from sys.sm$ts_avail t, sys.sm$ts_used u where t.tablespace_name = u.tablespace_name(+) order by 4 desc / […]
Read More Tablespace space with sm$ views + database level spaceIf archived redo logs are (wrongly) deleted/moved/compressed from disk without being backed up, the rman catalog will not know this has happened, and will keep attempting to backup the missing archived redo logs. That will cause rman archived redo log backups to fail altogether with an error like: RMAN-06059: expected archived log not found, lost […]
Read More rman crosscheck archivelog allVery useful SQL, from Tom Kyte: select decode(count(*), 1, ‘spfile’, ‘pfile’ ) from v$spparameter where rownum=1 and isspecified=’TRUE’ ; If it it using an spfile, an easy way to see the parameters is to generate an init.ora copy: SQL> conn / as sysdba (or connect as sysoper) SQL> create pfile = ‘/tmp/init.ora’ from spfile;
Read More Is database using pfile (init.ora), or spfile?Thanks to Laurent Schneider for tips on how to handle listener passwords in scripts. [Update 12-Jan-2007 – ammended script following Laurent’s comment, and update 09-Feb-2007 – ammended script following Kevin’s comment] A system startup/shutdown shell script I just used that copes with listener passwords is below. The location of the listener.ora file was /etc in […]
Read More 9i unix startup / shutdown with listener passwordThis shell script runs against every database that is up and running (has an entry in the ps list). Which I think is better than searching through oratab (since not all of those may be up and running, esp. on dev machines). You can run any SQL within it, but this particular version prints out […]
Read More Script run against every running database, listing total oracle memory (sga+pga)