Archive

Archive for the ‘Disaster recovery’ Category

Incremental restore from primary database to standby database

January 21, 2016 Leave a comment

If we have Dataguard, either with physical or logical standby, our databases will always be synchronized and we will be able to do switchover whenever we want.

Or not… maybe when we want to do a switchover, we notice that the standby database hasn’t been synchronized in months, and we need to restore the synchronization as soon as possible.

If the gap resolution is not an option, we will have to use RMAN in order to perform this operation. The steps will be the following ones:

1. On standby site, identify the minimum SCN used by all the datafiles:

SQL> select min(CHECKPOINT_CHANGE#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
———————–
262044906

2. On primary site, take an incremental backup of the database using a number a bit smaller than the smallest SCN obtained.

backup as compressed backupset incremental from scn 262044600 database format ‘/opt/oracle/install/restore_syncr/full_backup_mario_%U’;

3. Take also a backup of the controlfile:

backup as copy current controlfile format ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’;

4. Copy the files to standby site (scp, ftp, diskettes)

5. Execute the following commands on RMAN in order to apply the incremental backup:

shutdown immediate;
startup force nomount;
restore standby controlfile from ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’ ;
alter database mount;
catalog start with ‘+DATA/mario/datafile’ ;
catalog start with ‘/opt/oracle/install/restore_syncr/’ ;
switch database to copy ;

run {
allocate channel a0 device type disk ;
allocate channel a1 device type disk ;
allocate channel a2 device type disk ;
allocate channel a3 device type disk ;
allocate channel a4 device type disk ;
allocate channel a5 device type disk ;
allocate channel a6 device type disk ;
allocate channel a7 device type disk ;
allocate channel a8 device type disk ;
allocate channel a9 device type disk ;

recover database ;
}

6. Start the MRP process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ;

7. Enjoy your synchronized database and make sure that you don’t have to restore it again

How to read Oracle Datafiles without Oracle RDBMS

December 3, 2014 Leave a comment

In the following post we will explain how to access to the information of Oracle datafiles without the original database, only with the database files.

This is particularly useful in case that you had some kind of disaster and the information has to be recovered at any cost. Of course, making a restore with RMAN, Data Pump or other methods would be desirable… but we all know that some people just work with databases in production without a backup system right?

If you ask Oracle support how to make a recovery ONLY with some datafiles, they will tell you that this is impossible. However, by definition these files contain information, although it is encrypted. Let’s see how can we access to this information:

1. Trying to restore into another database.

There is a slight that you are able to restore a datafile from one database into another, just having the .dbf. The concept is that you need to modify the header of this datafile, to fool the database and make it belive that the datafile belongs to it.

You will still need a foresnic tool to modify the datafile and addapt it to a new database. But the advantage is that you will be able to make a clean export and restore it in a new database if you are successful.

Personally, I have never succeded with this method, although it would be technically possible.

2. DBF Viewer and Editor

There is an application for Windows that you can use to read directly the datafiles using ODBC or BDE. It’s called DBF Viewer and Editor, and you can find more information here:

http://dbf-viewer-editor.com/read-oracle-dbf-file.html

The license costs 71€, but you can try it for free, although you won’t be able to read the whole datafile.

3. BBED

For many years, BBED has been considered a secret tool, only known by a few, because it allows not only to read directly from datafiles, but also to write on them.

Nowdays, it is not included in Oracle installations anymore, but you can still use it on older versions.

The advantage of BBED is that it’s a free tool. It is really complex, and even if you are able to access the data, you can’t read this information easily, and much less perform selects. But if the information is really critical, and you don’t have any kind of backups, this could be a really valuable option.

I have personally used this tool, and I can assure that it works.

For more information, you can find a full explanation in this link:

http://orafaq.com/papers/dissassembling_the_data_block.pdf

Please keep in mind that this is the last resource, in case that restoring with RMAN or other backup methods is completely impossible.

Never try this tools in production, because overwritting a datafile will probably cause a database corruption, and you won’t be able to work with the database anymore. You should always work with a copy of the datafile.

Of course, there are more datafile foresnic methods. If you have worked with other programs and you have a better solution for this problem, please don’t hesitate to make a comment and share your knowledge with us.

ora-600

November 26, 2010 Leave a comment

If you don’t know what an ORA-600 is, you probably are new in the bussiness, or really lucky.

ORA-600 is the worst error you could face in an Oracle Database, and unfortunately, it’s more

common than people would want.

The description of the ORA-600 error is the following:

Error: ORA 600
Text: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s]
——————————————————————————-
Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
Action: Report as a bug – the first argument is the internal error number

When you get an ORA-600 in your database, a record will always be written into the alert log,

and you will probably have the reference to a trace file for debugging purposes. If you use

grid control, you will want to configure it in order to receive an e-mail whenever an ORA-600

is produced.

Basically, if you receive an ORA-600, you are recommended to open a SR in metalink (My Oracle

Support), but maybe you are facing a known error that can be solved without the intervention

of Oracle Support. Notice that you have to know what are you doing, if you lack the required

knowledge then you should open an SR and follow the help provided by the Oracle staff.

And a last piece of information: there is an undocumented oracle utility called ORADEBUG that

you could use under your responsability to troubleshoot ORA-600 errors. For more info, check

the following paper:

http://www.orafaq.com/papers/oradebug.pdf

I hope this information helped you in case you have found an ORA-600 in your database. And

remember, ORA-600 are almost always serious bussiness, so if you see this kind of failure, you

should assign the maximum priority to solve it!

Undocumented parameter _allow_read_only_corruption

July 7, 2010 4 comments

Some time ago we talked about the undocumented parameter _allow_resetlogs_corruption, which could help us to open a database in case we have corruption in our redo logs. In this post we will discuss another undocumented parameter that could be helpful in case of corruption: _allow_read_only_corruption.

This parameter should not be used without the support of Oracle itself. But if you want to do some tests to retrieve your data more quickly, you can make a copy of your corrupted database and try this hidden parameter. Just make sure that you have a copy, because playing with this kind of parameters you can corrupt your database much more.

The procedure to use this hidden parameter would be to shutdown abort your database to make sure it’s completly closed.

Then, startup the database in nomount mode, and set this parameter to on:

ALTER DATABASE SET _allow_read_only_corruption SCOPE=SPFILE;

Shutdown immediate the database, and then try to startup your database “normally”. It will open your database even if you have some corruption, or at least, it will try to do it.

The first thing you should do after opening the database in this mode is to export all the data, and recover as much as you can. You shouldn’t use the database in this instance as this is an inconsistent mode, and _allow_resetlogs_corruption.

Just be careful, and remember that if you use this kind of knowledge without knowing what you are doing, you are claiming for disaster.

Undocumented parameter: _allow_resetlogs_corruption

May 6, 2010 1 comment

Note: This is an undocumented paramete and shouldn’t be used if you don’t know what you are doing. You can put your database in an unstable state if you don’t know what you’re doing, so be careful. You have been warned…

_allow_resetlogs_corruption is a hidden parameter, and undocumented for mortals like us, that is only supposed to be used by Oracle Staff in case of emergency. If you need to use it by yourself, you should have a cold backup ready to restore, and NEVER use it on a working database that you don’t wanna lose, because it’s very probable that you will have corruption problems afterwards.

To use this parameter, you have to set it into your spfile or pfile the following way:

_allow_resetlogs_corruption=TRUE

By default, it is set to false, of course.

When to use this parameter?

You may be in need to use the undocumented parameter _allow_resetlogs_corruption if you have a database that has crashed and cannot be recovered because one of your redologs is corrupted. Yes, you could make an incomplete recover, but… what if you MUST have the data that is inside your corrupted redo logs? What if you can’t afford to lose that data, or at least, you must try to recover it when humanly possible?

Well, in that case then you can try to use this parameter, because you have nothing to lose. However, make sure that you have a cold backup of your broken database, or you could miss any chance of recovering your data.

And just in case you are wondering, yes, you have other ways to recover the data from a “broken” database, but we will talk about this kind of disaster recovering tools in the future.

To activate the resetlog corruption and try to recover all the data that you can, you must:

1. STARTUP MOUNT;
2. ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE;
3. Cross your fingers.
4. ALTER DATABASE OPEN;

If your database opens, DON’T PUT IN IN PRODUCTION. Extract all the data that you need and recover it by any means, for example, using exp or data pump. You could have problem extracting the data, but if you have managed to open the database, you should be able to make an export.

However, it’s best if you don’t ever need to use this kind of parameters. But, in case you have problems with your database, I hope this post will help someone to recover it in case of disaster.

Be careful and don’t play with your production environment!