Doing a repeated rman database time based recovery

RMAN>
run{
…
alter database mount;
…
set until time “to_date(‘27.10.2008 23:30:00′,’DD.MM.YYYY HH24:MI:SS’)”;
…
restore database;
recover database delete archivelog;
alter database open resetlogs;
}

without rman catalog repository can fail with error:

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

To solve the issue

• list the incarnation id of the database

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations

1   1   ORAV10  76765624 PARENT  1         22.11.2007 16:44:40
2   2   ORAV10  76765624 PARENT  4921103   09.04.2008 11:38:07
3   3   ORAV10  76765624 ORPHAN  15854298  27.11.2008 11:43:33
4   4   ORAV10  76765624 CURRENT 15854298  27.11.2008 17:38:11

• set the database in mount status to incarnation id that is fitting to the selected restore time

 
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> reset database to incarnation 2;

• repeat the database time based recovery

Be careful: Do not restore controlfile. If you do so, incarnation is set the latest id and you run into same error.

RMAN> list incarnation;

List of Database Incarnations

1   1   ORAV10  76765624 PARENT  1         22.11.2007 16:44:40
2   2   ORAV10  76765624 PARENT  4921103   09.04.2008 11:38:07
5   5   ORAV10  76765624 CURRENT 15783824  10.12.2008 16:03:56
4   4   ORAV10  76765624 ORPHAN  15854298  27.11.2008 17:38:11
3   3   ORAV10  76765624 ORPHAN  15854298  27.11.2008 11:43:33

In some cases you get errors during recovery
ORA-00283: recovery session canceled due to errors
ORA-00081: address range [0x60000000000A7D70, 0x60000000000A7D74) is not readable
ORA-00600: internal error code, arguments: [kcvsor_current_inc_rdfail], [0], [], [], [], [], [], []

In this case you have to restore an adequate controlfile created in selected recovery time frame and that has the same incarnation as the datafiles (see also oracle support Note:378273.1)

Being forced to work with MySQL Databases, not every Web Developer is familiar with databases in general.

Now the good news is that when working in a LAMP (Linux, Apache, MySQL and PHP) environment you do not really need to have deep knowledge about relational databases to succeed.

Most tools like CMS do a full automated setup for you and the only knowledge it needs to have starting with them, is the server and database name and finally the login credentials (username/password) to access it.

Just later on the one or other maintenance work will occur and you will feel the need of having some nice and neat database interface to work with. Knowing about this need today I will recommend you a few tools and you should feel free to choose one or each of them based on your platform and taste.

The first tool I will recommend you is called phpMyAdmin. It is written in PHP and as a so called browser tool it works platform independent and flawless in all common browsers. Very often being offered by hosting providers it is mostly already part of your hosting package and easy to access and use. Coming along with all necessary options like browsing and navigating through your databases, it also offers a SQL interface and the often necessary export/import options. Personally it is my favorite tool to work with.

Once you are familiar with phpMyAdmin, phpMyBackupPro is another very similar tool you might find it worth to look at. Also browser based it is mainly written for interactive exports and imports, but can get also use for scheduled automatic backups.

For Windows platform only I would like to recommend you a tool called MyDB Studio. Coming along with a well designed interface it offers all features you need and the license it needs is given away for free in case you only use it for private and non commercial interests. It just needs you to give them a valid email address and the key is emailed to you quickly after.

And now finally and last in case you are such a Macintosh guru, I would like to recommend you CocoaMySQL. It does a similar great job like the others and can get used right away after the installation has taken place. What I like is the console view of is, showing you all command issued during the past and looking at it you might even learn some SQL (Structured Query Language).

When transferring data from one server to another – maybe for backup reason – most people use tar to create an archive. Then they copy it to the new server and untar it or leave it as a tarfile as backup.

Working with UNIX and using a trick  you can transfer it with compression and speed up the whole transfer time a lot.

Using the output result of  a command directly as the command line input of a second command  using a  Unix Pipe the need of writing it down to disk first no longer exists. Finally accessing the remote system though a ssh tunnel will even transfer the compressed data over the network within one single step.

Invoking the following single command will transfer all your data within sourcedirectory an Server B and immediately store it locally  on Server B within a compress archive:

ssh username@hostname “cd /sourcedirectory/ ; tar czf – .” > /targetdirectory/filename.tgz

While the following variation:

ssh username@hostname “cd /sourcedirectory/ ; tar czf – .”> /targetdirectory/filename.tgz| tar xvf –

will directly extract it again for you on the your local system.