Archive for February, 2007
Of course this would never happen in the real world
Friday, February 23rd, 200710046 tracing in another session
Wednesday, February 14th, 2007To switch 10046 tracing on in another session (first setting on timed statistics and making max_dump_file_size very large):
set pages 9999 verify off col serial new_value serial noprint select serial# serial from v$session where sid = &1 ; exec sys.dbms_system.set_bool_param_in_session(&1,&serial,'timed_statistics',true) exec sys.dbms_system.set_int_param_in_session(&1,&serial,'max_dump_file_size',999999999) exec sys.dbms_system.set_ev(&1,&serial,10046,8,'')
And to switch it off:
set pages 9999 verify off col serial new_value serial noprint select serial# serial from v$session where sid = &1 ; exec sys.dbms_system.set_ev(&1,&serial,10046,0,'')
Note that you can run new (9i+) tkprof against old (8i and below) 10046 trace files in order to report the wait information. It is also possible to cat multiple trace files together before tkprof’ing.
Based on Alexander Bubernak’s post at http://www.dbasupport.com/oracle/ora10g/10046event.shtml
Clone a database
Friday, February 9th, 2007The 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.
Database clones can also be done with rman, which has the advantage of avoiding the performance draining hot backup mode.
set pages 9999 lines 132 serverout on size 99999
BEGIN
FOR t IN ( select distinct tablespace_name from dba_data_files )
LOOP
dbms_output.put_line('alter tablespace '||t.tablespace_name||' begin backup ;') ;
FOR f IN ( select file_name from dba_data_files where tablespace_name = t.tablespace_name )
LOOP
dbms_output.put_line('host cp '||f.file_name||' '||f.file_name ) ;
END LOOP ;
dbms_output.put_line('alter tablespace '||t.tablespace_name||' end backup ;') ;
END LOOP ;
END ;
/
set lines 80
select 'alter tablespace '||tablespace_name||' add tempfile '||file_name||' size '||bytes/1024/1024||' m ;'
from dba_temp_files
/
prompt alter system switch logfile ;;
prompt alter system backup controlfile to trace ;;
Slow SQL report
Wednesday, February 7th, 2007Lists 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.
Silent installer on unix still needs X windows until 10g
Friday, February 2nd, 2007It’s not always easy to get X windows properly, or to get hold of X windows emulator software for Windows PCs. So it was a bit of a step backwards when the installer moved away from the ugly but effective clunky text display that it had in version 7 and below.
Running the installer in silent mode should have been a way around the need for X displays, but in fact it still fails (v8 through 9iR2) with an error like:
Exception java.lang.NullPointerException occurred..
So, even though you’re not going to use the installer GUI, oracle (for those versions) makes you have X windows all set up anyway. Quite annoying.
But – it’s fixed in 10g!
So oracle versions up to 7, and then from 10gR1 and above, don’t need X windows to install. Versions 8 up to 9iR2 do.
10gR2 Installation Guide says:
A.1.1 Reasons for Using Silent Mode or Noninteractive Mode
Use silent mode if you want to:
- Complete an unattended installation, which you might schedule using operating system utilities such as at
- Complete several similar installations on multiple systems without user interaction
- Install the software on a system that does not have X Window System software installed on it
And 10gR1 Installation Guide for Unix Systems:
Automated Installation Methods Using Response Files
By creating a response file and specifying this file when you start the Installer, you can automate some or all of the Oracle Database installation. These automated installation methods are useful if you need to perform multiple installations on similarly configured systems or if the system where you want to install the software does not have X Window system software installed.
To run an install in silent mode, copy a template response file from Disk1/response/*.rsp on the installation media, and edit it to include the install choices you want. Then run the installer with options:
./runInstaller -silent -responseFile filename.rsp
Actually there’s a little more to it than that, but see the installation guide for the full details.
Using 10g datapump and scheduler to copy schemas
Thursday, February 1st, 2007Update 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 this post.
For a nightly process to copy small schemas from one database to another, the normal, or old style, way to do that is with export/import in a cron controlled shell script, which would include a sql-from-sql script to drop all the target schema objects before doing the import.
But that presents problems in RAC, which is what I was working on – which node does the cron script reside on? What happens if that node is down?
An option would be to have the script run on another machine, one unrelated to the RAC cluster, but that introduces more points of failure, and means sending data back and forward over sql*net.
So instead I used the oracle’s 10g scheduler and datapump, as below. This data pumps straight over a database link, without having to write to a dump file in between, which is nice. And because it is all in the database, it ought to be unaffected by particular RAC nodes being down.