Switch a RAC database into archivelog mode:
alter system set log_archive_dest_1='location=/ORA_DISK/redoarch/arch_' scope=both sid='*' ; alter system set cluster_database=false scope=spfile sid='mydb1'; srvctl stop database -d mydb startup mount alter database archivelog ; alter system set cluster_database=true scope=spfile sid='mydb1'; shutdown immediate srvctl start database -d mydb
Sample restore from command line Tivoli Storage Manager. Run as root.
ps -ef | grep dsm #here look to see if 'optfile' is set cd /mydir dsmc -optfile=/opt/tivoli/tsm/client/ba/bin/dsm-ora.opt tsm> restore /mydir/*.dbf -inactive -pick tsm> 1 #here choose which file number you want tsm> 2 #choose another file if needed tsm> o #o='ok' tsm> quit
You should specify an ‘optfile’ at line 3 only if you saw it in the output of line 1.
Continue reading
Windows Server 2008 by default has a firewall that does not allow remote database connections. Fix is at http://technet.microsoft.com/en-us/library/ms175043.aspx
run {
set until time "to_date('21-OCT-2010 14:45:00','DD-MON-YYYY HH24:MI:SS')";
restore database ;
recover database ;
alter database open resetlogs ;
}
(H/t Stefan Knecht)
select name, refgroup,rname from dba_refresh_children where name in ('MV1','MV2');
exec dbms_refresh.subtract('RGOWNER.RG1', 'MVOWNER.MV1')
exec dbms_refresh.add('RGOWNER.RG2', 'MVOWNER.MV1')
HTML format emails allows images, fonts, colours, hyperlinks. It can be done with utl_smtp (after the jump) and with utl_mail Continue reading
Avoiding Mistakes with ITIL (IT Infrastructure Library)
ITIL is best practice advice and guidance. It is a common mistake to take as prescriptive. Needs adapted/customised to be organisation specific. So is not a silver bullet. Not a standard. You cannot be ITIL compliant. You need to adopt and adapt components of ITIL. Decide on the ITIL tools you are going to use and then modify them to suit. Continue reading
This allows you to get code listings for all code (procedures, views, materialized views, etc.) that reference particular tables:
set long 200000 pages 0 verify off lines 131 feed off
column txt format a121 word_wrapped
column spoolfile new_value spoolfile noprint
spool go.tmp
select '@2 '|| decode(type , 'MATERIALIZED VIEW' , 'MATERIALIZED_VIEW' , type) ||' '||name||' '||owner
from dba_dependencies where referenced_name in ('TABLE1','TABLE2','TABLE3')
order by 1 ;
spool off
@go.tmp
That calls files 2.sql which contains:
select lower( '&1' ||'.'|| '&2' || '.sql' ) spoolfile from dual ;
spool &spoolfile
select dbms_metadata.get_ddl('&1','&2','&3') txt from dual;
spool off
Send email from unix with attachment, which in this case has been zipped with a password:
zip -P mypassword /tmp/myfile.zip myfile.csv mutt -a /tmp/myfile.zip -s "Zipfile Attached" user@email.com < /dev/null
Make indexes unusable before doing a big insert, then rebuild them at the end, is faster than insert with indexes in place and no risk of forgetting to recreate a dropped index:
create table af ( mycol varchar2(100) ) ; create index af1 on af ( mycol ) ; alter index af1 unusable ; insert into af (mycol) select * from big_table ; select status from dba_indexes where index_name = 'AF1' ; alter index af1 rebuild ; select status from dba_indexes where index_name = 'AF1' ;
Code to make a lot of indexes unusable in one go is:
select 'alter index '||owner||'.'||index_name||' unusable ;' from dba_indexes where owner = 'MYSCHEMA' ;