Blog Archives

Rman list most recent backups

List most recent rman database backups on target database sqlplus with: set pages 9999 lines 132 ALTER SESSION SET nls_date_format = ‘Dy DD-Mon-YYYY HH24:MI:SS’ ; SELECT b.checkpoint_time , b.incremental_level , COUNT(*) FROM v$backup_datafile b JOIN v$datafile f ON f.file# =

Posted in Rman

Flashback query alternative to point in time recovery

If data has been removed due to user error, it can be easily and quickly be brought back without the need for restores, using flashback query: CREATE TABLE temp_rescue AS SELECT * FROM owner.table AS OF TIMESTAMP TO_TIMESTAMP ( ‘Thu

Posted in Rman

RMAN point in time restore

Rman point in time restore: rman connect target / connect catalog user/password@catalog_database run { shutdown immediate ! now take an OS copy of current control file and current online redo logs in case need to regress startup mount set until

Posted in Rman

Upgrade rman catalog – no need to upgrade oracle database version

Rman catalog can be upgraded to a higher version than its hosting database oracle version. This is useful if you want to back up new versions of oracle without changing the oracle version on the database used to host the

Posted in Rman

IBM Tivoli restores using dsmc

As root dsmc or, for more complex configurations, specify an optfile: dsmc -optfile=/opt/tivoli/tsm/client/ba/bin/dsm-ora.opt List backup files: tsm> q backup “/oracle/*” -subdir=yes -inactive Restore: tsm> restore “/oracle/mydb/*” /oracle/ -subdir=yes -pick Note how the second mention of directory path ommits the lowest

Posted in Linux, Rman

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

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

Data block corruption cleared with alter system flush buffer_cache

So I had: ORA-08103: object no longer exists being reported on SQL affecting one application table. But that table existed ok in dba_tables, could be described ok, and selects restricted to its indexed columns returned data ok. Suspicion was some

Posted in Bugs, Rman