Thursday 17 November 2011

Bkp Recovery Category


Archive for the ‘Bkp n Recovery’ Category
The following are the steps for performing database cloning using cold backup
Assumptions : You are using Linux flavour OS and following same directory structure
1. Take the cold backup of source database
2. Take controlfile trace and pfile or spfile (that was using by the source database)
3. Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database
4. Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp
5. Place pfile or spfile in “dbs” directory on target
6. Copy the remaining files to their respective locations (If any directories are missing, do create them)
7. Open bash_profile file and set ORACLE_HOME and ORACLE_SID
8. Connect as sysdba and Startup the database
Many a times, while performing recovery of controlfile or redolog files, after opening the database with RESETLOGS option, you may land up in getting ORA-01194 file 1 needs more recovery to be consistent error.
In such situations, issue
sql> recover datafile 1;
while recovering it will ask for archive log which sometimes doesn’t exist. Then provide the redolog file path and if the data still exists in redolog file, oracle will recover the database and resetlogs option will work fine at that moment
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
run
{
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’
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
As we know when we remove file at OS level while trying recovery scenerio in 10g, it will not throw error and you can continue to work as normal. Please find the following links for why it is so?
We have a server in active/passive setup (active/passive is just like standby database concept but at OS level). Due to network problem all of a sudden database was failed over to passive server (node 2). on client request, we switched it back to active server (node 1). After starting the database, i observed the following error in my alert log file
ORA-00600: internal error code, arguments: [2141], [189375672], [193262834], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [58], [55], [], [], [], [], []
Doing block recovery for fno: 2 blk: 67751
The above error represents that a block in file#2 i.e generally undo datafile was corrupted and database was trying to recover that block. because of this transactions (even select queries) are getting hanged.
As a resolution, we raised a service request with Oracle support (infact, it is a must step that we raise service request with Oracle support for all ORA-600 errors). They suggested to restore the datafile from the latest backup and recover using archives. i used following steps for that
rman> run
{
shutdown immediate;
startup mount
allocate channel c1 device type ‘SBT_TAPE’;
restore datafile 2; -> you can use restore tablespace undotbs_name also
recover datafile 2;
sql ‘alter database open’;
}
Hope this post will be helpful if you face same kind of issue
Hi Folks, Long back i posted steps for database cloning using cold backup. In that post, we assumed that directory structure is same on both the server (ofcourse OS version also :-))
Now, the below steps will let you understand how we can perform database cloning using cold backup, when you are not following the same directory structure in the target machine (Remember, here also OS is same)
1. Take the cold backup of source database
2. Take controlfile trace and pfile or spfile (that was using by the source database)
3. Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database
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
5. Place pfile or spfile in “dbs” directory on target
6. Copy the remaining files to their respective locations (If any directories are missing, do create them)
7. Open bash_profile file and set ORACLE_HOME and ORACLE_SID
8. Connect as sysdba and Startup the database in nomount stage
9. 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.
10. 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.
11. Finally, Open the database.
Long back i posted a voting question in my blog…
which process updates controlfile, when doing complete recovery of it?
I got results as follows
 http://pavandba.files.wordpress.com/2010/10/12.jpg?w=149&h=382
But unfortunately max votes got for a incorrect option. The correct answer is Server process.
Many DBA’s don’t know that we can perform complete recovery when we lost controlfile. (even i had some good argument with a friend on my blog on this)
If you want to know how to do complete recovery, see below link
http://pavandba.wordpress.com/2010/03/18/how-to-do-complete-recovery-if-controlfiles-are-lost/
By reading above post, you might have got the point that we are creating new controlfile. In such cases, to open the database we require latest SCN to be there in controlfile to match it with datafiles and redolog files.
If it doesn’t match, it will fail to open. So server process will take that responsibility to update the controlfile with latest SCN and this info will be taken from datafiles
Folks,
really long time to see you…here is the new post of mine
You know…it is not necessary to take undo tablespace backup either in cold backup or hot backup. But ofcourse many of DBA’s will include that in their script…
Now a big question…if undo is not being backed up and lets say i need to do instance recovery or database recovery and need to rollback a transaction, how that will happen.
Here is the answer…
when you do some transaction, redo entries will be generated..accepted ! Just like that whenever some changes happend to undo tablespace (or more clearly undo segments) oracle will generate redo entries.
So even though you doesn’t backup undo, you have the redo entries through which you can recover or rollback the transactions.
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>
or
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 … :-)
Below steps helps you in performing database cloning using hot backup
Assumptions:
1. directory structure is different in both source and target servers
2. Oracle version : 10.2.0.4
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;
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 : 10.2.0.4, 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
spool scandatafile.sql
set serveroutput on
declare
  scn number(12) := 0;
  scnmax number(12) := 0;
begin
  for f in (select * from v$datafile) loop
    scn := dbms_backup_restore.scandatafile(f.file#);
    dbms_output.put_line(‘File ‘ || f.file# ||’ absolute fuzzy scn = ‘ || scn);
    if scn > scnmax then scnmax := scn; end if;
  end loop;

  dbms_output.put_line(‘Minimum PITR SCN = ‘ || scnmax);
end;


No comments:

Post a Comment