Oracle of Nashville

If you have Streams configured in your Oracle database environment, you probably know that RMAN is "Streams aware", meaning, it knows which archive logs Streams still needs and won't remove those from disk once backed up. There are however RMAN commands that will remove archive logs even if they're still needed by Streams. The problem I encountered the other day was with this RMAN command to backup my archive logs, which I run every 15 minutes: run { backup archivelog all delete input; } My archive logs were removed from disk after being backed up, but not if needed by Streams.  The problem was my backup files were getting cumulatively larger throughout the day, because every time it ran, RMAN backed up every archive log still on disk.  This quickly filled the disk. I only wanted to backup archive logs that had not already been backed up, so I tried this: run { backup archivelog all not backed up 1 times delete input; }  My archive backups were no...

Several weeks ago we had major corruption across the file-system on our production database server.  Everything was affected--Oracle home files, database files, logs...everything. After I restored the database, I used RMAN to search for corruption in the instance. RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL; This command will validate all database and archive log files that would be backed up, checking for physical and logical corruption.  No backup is actually done with the command. When corruption is found, it can be seen in  V$DATABASE_BLOCK_CORRUPTION . My output was the following: FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE ---------- ---------- ---------- ------------------ ------------------------------------ 15 2061570 126 9709077348 NOLOGGING 15 2061442 126 9709084124 NOLOGGING Corruption was found, but the corruption type wasn't shown.  I also couldn't find the objects that were corrupted. SELECT owner, segment_...

Rsync is a useful Unix tool for keeping directories in synch between two server.  I use it to copy Oracle archive logs from a production server to a test server. This command runs on my test server from the oracle user's crontab every 5 minutes.  It copies the files via SSH.  New files from the source directory are copied to the test backup directory.  If a file is deleted from the source directory, it is NOT removed from the target directory.  I have it setup this way because I want to control that manually.  I have a separate cronjob that purges archive log files over 3 days old. rsync -rave "ssh -l oracle" sourceServer:/u06/oradata/PROD/arch  /u06/oradata/prodArchBkp Rsync also preserves the timestamp of the source file in the target directory. You can also use rsync to delete files in the target directory that do not exist in the source directory. rsync -rave "ssh -l oracle" --delete sourceServer:/u06/oradata/PROD/arch  ...

I tested our previously mentioned DR solution a few days ago. Here's what I did: First, I took a hotbackup of our production database (PROD) and moved it to our test server. I then created a new stand-by instance on the test server, started it up in nomount specifying the pfile, and altered it's DBID to match the DBID of PROD. This allows the DR database to apply the archived redo logs. RMAN> SET DBID XXXXXXXXXXXX; Next, I restored the controlfile from PROD's backup and mounted the stand-by database. RMAN> restore controlfile from '/oback/control_PROD_ctl'; RMAN> alter database mount; RMAN> exit; The next step was to restore the PROD datafiles. RMAN> restore database; After some archived redo logs appeared on PROD, I copied them to the test server and applied them using RMAN. RMAN> recover database; The database recovered using all of the available archived redo logs. It did throw an error saying it was looking for the next log which had not bee...

RESTORE is used to restore the datafiles from a backup. You can restore up to the time of the backup or a point before using SET UNTIL . You would lose any changes made to the database after the backup by performing only a RESTORE. RECOVER is used to roll the database forward after a RESTORE by applying the redo logs. In essence, RECOVER = RESTORE + redo logs. The following example assumes you have lost your control files and datafiles, but still have your redo logs. $ rman target / nocatalog RMAN> startup nomount; RMAN> restore controlfile from '/oback/controlfile'; RMAN> alter database mount; RMAN> restore database; RMAN> recover database; RMAN> alter database open resetlogs;