Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Bkp n Recovery’ Category

How to resolve rman: can’t open target?

Posted by Pavan DBA on June 26, 2010

Sometimes we may get “rman: can’t open target” error while trying to connect to rman.

we would be wondering because our database will be up and running and listener also when checked.

The cause to the above problem is having “/usr/X11R6/bin” in the PATH variable. If we remove that, it will work.

1) echo $PATH
2) export PATH=<specify entire path, but remove the above mentioned path>
3) edit .bash_profile file and enter the path without “/usr/X11R6/bin”
4) save the .bash_profile file

Now start your rman, it will start working … 🙂

Posted in Bkp n Recovery | Tagged: , , | 2 Comments »

Database cloning using hot backup (if directory structure is different)

Posted by Pavan DBA on March 26, 2010

Below steps helps you in performing database cloning using hot backup
1. directory structure is different in both source and target servers
2. Oracle version :
3. OS version : Linux 5
4. target database name is same as source database name

step 1 :  Take the hot backup of source database
sql> alter database begin backup;
$ copy datafiles to backup location
sql> alter database end backup;

step 2 : Take controlfile trace and pfile or spfile (that was using by the source database)

step 3 : Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

step 4 : Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp
Note: Not necessary in copying control files and redologfiles

step 5 : Place pfile or spfile in “dbs” directory on target

step 6 : Copy the remaining files to their respective locations (If any directories are missing, do create them)

step 7 : Connect as sysdba and Startup the database in nomount stage

step 8 : Edit the trace file (that was copied) and generate a create controlfile script from it. Modify the script and specify the new locations of  the files.

step 9 : Execute the controlfile script which will create controlfiles in the location specified in CONTROL_FILES parameter. Once control files are created, database will be forwarded to MOUNT state.
sql> @create_controlfile.sql

step 10 : Finally, Open the database with resetlogs option
sql> alter database open resetlogs;

Posted in cloning | Tagged: , , | 5 Comments »

Database cloning with hot backup (if directory structure is same)

Posted by Pavan DBA on March 26, 2010

Here are the steps for performing database cloning using hot backup to a different server. I am assuming you are using same directory structure in the target server also.
Assumed Oracle version :, OS version : Linux 5

Step 1 : Take database hot backup as follows
sql> alter system switch logfile;
sql> alter database begin backup;
$ cp *.dbf to backup location (as it is hot backup, we will not take backup of redolog files)
sql> alter database end backup;
sql> alter system switch logfile;
$ cp *.ctl to backup location
Note: If you are using 9i database, use “tablespace begin backup/end backup” clauses

step 2 : Take backup of spfile or pfile of source database and also archives

step 3 : Install oracle software in target server (select “software only” option in OUI)

step 4 : copy the files to target server either using FTP or any methods

step 5 : place pfile or spfile in dbs directory

step 6 : copy all files (datafiles, controlfiles and archives) to respective locations

step 7 : do the following
sql> startup nomount
sql> alter database mount;
sql> recover database using backup controlfile until cancel;
here it will ask to apply archives and will give suggestion as file name and path. apply required archives

step 8 : finally, open your database with resetlogs option
sql> alter database open resetlogs;

Sometimes you may get following error while doing hot backup cloning

ORA-01194 file 1 needs more recovery to be consistent error

in such cases, do a switch logfile in source server and copy & apply that archive logfile in target server

Posted in cloning | Tagged: , , | Leave a Comment »

until time reovery using RMAN

Posted by Pavan DBA on March 18, 2010

Generally until time recovery will be used to move the database to a particular time in the past (from 10g you can do the same using FLASHBACK which we will discuss later). Maximum times we will do this because of loosing important table.

One thing we need to keep in mind before performing until time recovery is to get acceptance from all the users as it will rollback their changes

The following are the steps to do that using RMAN

set until time ‘2010-03-18:12:00:00′;
restore database;
recover database;

sometimes you may get below error

RMAN-03002: failure of set command at 09/25/2007:11:08:52
ORA-01861: literal does not match format string

this is because your NLS_DATE_FORMAT is not set correctly. in such case, first export your NLS_DATE_FORMAT parameters and then invoke RMAN

export NLS_DATE_FORMAT=’YYYY-MM-DD:hh24:mi:ss’

Posted in Bkp n Recovery | Tagged: , , | Leave a Comment »

steps to recover noarchivelog mode database

Posted by Pavan DBA on March 8, 2010

Below are the steps to perform noarchive log mode database recovery.

I am assuming a datafile or some datafiles are missing and you have a full database cold backup

  1. shutdown immediate;
    # this you need to do if database is not already shutdown
  2. copy all the files (datafiles,redolog files and controlfiles)from backup to original location
    # this is called restoration
  3. startup the database

Note: as we are restoring old backup, there will be data loss and how much data is lost depends on how recent your backup is

Posted in Bkp n Recovery | Tagged: , , | 5 Comments »

%d bloggers like this: