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# = b.file# JOIN v$tablespace ts ON f.ts# = ts.ts# WHERE ts.included_in_database_backup = ‘YES’ AND b.checkpoint_time > […]

Read More Rman list most recent backups
November 23, 2015

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 14-Nov-2013 13:00:00’ , ‘Dy DD-Mon-YYYY HH24:MI:SS’ ) ; Or if the entire table has been […]

Read More Flashback query alternative to point in time recovery
November 18, 2013

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 time “to_date(‘Mon 04-Nov-2013 09:00:00′,’Dy DD-Mon-YYYY HH24:MI:SS’)” ; restore database preview ; — optional, to check […]

Read More RMAN point in time restore
November 6, 2013

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 rman catalog. To do this, run the following commands from the higher version oracle database: […]

Read More Upgrade rman catalog – no need to upgrade oracle database version
March 2, 2013

One Comment

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 directory. tsm> +   # to select all tsm> o   # ok, start restore

Read More IBM Tivoli restores using dsmc
December 23, 2010

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 the -delete option, so instead: # Delete files older than 15 minutes find /ORA_DISK/redoarch/ -name […]

Read More Find delete old files
October 29, 2010

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 into noarchivelog mode: srvctl stop database -d dbname startup mount –only one instance alter database […]

Read More RAC alter database archivelog
October 28, 2010

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 you want tsm> 2 #choose another file if needed tsm> o #o=’ok’ tsm> quit You […]

Read More Tivoli Storage Manager simple restore
October 28, 2010

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)

Read More RMAN set until time recover
October 22, 2010

One Comment

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 sort of data block corruption. Rman backup logs had not reported any corrpution, but dbverify […]

Read More Data block corruption cleared with alter system flush buffer_cache
April 19, 2007

2 Comments