Rman

Of course there is nothing special about restoring a database from the cloud on your own down-on-earth server, but it helps making this post cool and cloudy. When I deployed XE to an AWS instance I made a small script to backup the database once pr day (scheduled in crontab), and another to fetch the backupset to my server here on earth. Backup script looks like this: #!/bin/sh ORACLE_SID=XE ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID ORACLE_HOME PATH rman target / << EOF > $HOME/script/rman.$$ backup database plus archivelog delete input; delete noprompt obsolete; list backup of database summary; EOF By default the XE is installed without archiving so you need to change that when you install your production XE database. I also turned on auto backup of the controlfile. The RMAN configuration for the XE database looks like this: ...

Recently during restore of a large database (over 10 TB) we monitored the alert log looking for messages like «Full restore complete of datafile 42 to datafile copy … Elapsed time: 0:39:32» We saw that the time to restore a 32GB file varied a lot, from around 20 minutes to around 75 minutes. This made me wonder why. We noticed that some of the files belonging to the UNDO tablespace took usually little time, and then I found a couple of more data files that took little time to restore. Then I thought that maybe the UNDO tablespace at the time of the backup had many unused blocks (because there weren’t many active transactions at the time). So I checked the other two files that did not take long time to restore and found out they had lots of free space in them too. ...

After you have validated database as shown in previous post any corrupt database blocks will be reported in the view V$DATABASE_BLOCK_CORRUPTION. Now you want to find out the tables that used to have data in these blocks; those data have typically been lost if you have no backup of them or do not posses special tools like DUDE . Before you start I recommend to purge the DBA Recycle Bin if it is in use, as reported here certain queries seem to take longer time when it is not empty. Execute as SYSDBA: ...

I worked with corrupt blocks in a database a few weeks ago and decided to write three posts about it so I don’t have to rethink all of it next time it happens. Hardware and the database have improved a lot, because I rarely run into this problem. The first and obvious thing to state is that corrupt blocks in the database should be detected early by proper fault management (if you use Enterprise Manager you are good). When detected it should be fixed by using RMAN, either by command: ...

You may get error ORA-32012 if you are on 11g, but have the compatible parameter set to pre-11g in the database you are cloning from, and the spfile for the source database is stored in ASM when you do an RMAN duplicate from active database. To get around this error, I have found two workarounds: Set the value for compatible to at least 11.1 Skip transferring of the spfile during the clone process. Option 1 is a big change for the database (it affects the CBO among other things), but say you need a clone and are going to change this parameter anyway it is the simplest one since you don’t have to create the parameterfile manually. Option 2 means you create the spfile before the cloning starts and remove the clause with spfile from the duplicate command. ...

Using the CONVERT command in RMAN can be an efficient way to migrate a database from one OS to another, if they have the same endian. We migrated 9 databases on 10.2.0.4 from HP-UX (on Itanium) to AIX. The databases where distributed on three servers with not exactly the same hardware configuration. On one of the servers we encountered an error described in bug 888530 on Metalink or My Oracle Support. The process terminated prematurely with ORA-7445 when converting an undo tablespace. The other tablespaces were migrated fine, but the undo tablespace ended up with a missing datafile. After the error occurred the RMAN session was left hanging and after killing it the migrated datafile belonging to undo was removed; may be that had something to do with the file system being NFS mounted. Anyway, our solution was to create another undo tablespace and change the undo_tablespace parameter, and then dropping the corrupted one. In one of the tests we did, we managed to do recovery on the migrated undo tablespace, but some funny error messages showed up in the alert log, probably because it was not finally migrated to the new platform. Meaning, if you get this error, create a new tablespace for undo and drop the old one.

After the incident with missing forced logging in primary database the logical standby had to be created again. Compared to what is normal these days the database is not huge, but the standby database remains on a server in a different city and the link between isn’t exactly a digital super highway. Also I had to restrict the consumed bandwidth during transfer with secure copy (scp -l 2500, restricts to 2500 kbs) or else connections to the primary database might suffer. ...