Month: October 2010

Recover RAC database

RAC has to be switched to non-cluster temporarily for the recovery. startup nomount alter system set cluster_database=false scope=spfile sid=’*’ ; shutdown immediate startup mount recover database alter database open ; alter system set cluster_database=true scope=spfile sid=’*’ ; shutdown immediate srvctl

Posted in RAC

Remove a parameter entry from spfile

alter system reset log_archive_start scope=spfile sid=’*’ ; Much better than old versions where you had to temporarily create a pfile, edit that, and load it in as an spfile.

Posted in Spfile

Find delete old files

# Delete files older than 15 minutes find /ORA_DISK/redoarch/ -name ‘arch_*.dbf’ -mmin +15 -delete #Delete files older than 2 days 50 8 * * * find /ORA_DISK/redoarch/ -name ‘arch_*.dbf’ -mtime +2 -delete Old versions of unix find do not have

Posted in Linux, Rman, Scripts

RAC alter database archivelog

Switch a RAC database into archivelog mode: srvctl stop database -d dbname startup mount –only one instance alter database archivelog alter database open srvctl start database -d dbname –this will start the remaining instances on the cluster And switch it

Posted in RAC, Rman

Tivoli Storage Manager simple restore

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

Posted in Linux, Rman

Windows 2008 remote database connections

Windows Server 2008 by default has a firewall that does not allow remote database connections. Fix is at

Posted in Installs, Windows

RMAN set until time recover

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)

Posted in Rman

Move MV from one refresh group into another

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’) List refresh groups with: select rowner, rname, count(*) from dba_refresh_children group by rname, rowner order by 1,2;

Posted in Materialized views