Label

Friday, June 22, 2012

RMAN commands for different purposes.

Recover whole oracle database .

SQL> startup force mount;
Now from the RMAN prompt.
run{
restore database;
recover database;
alter database open;
}

Backup database.

run {
configure device type disk backup type to compressed backupset parallelism 2; 
backup database plus archivelog;
}

Whenever we issue the above mentioned command to have a backup by default first of all RMAN will  check each and every block for physical block corruption then it will allow to backup the block on disk, the moment it have encountered, backup will be terminated. To avoid check for physical corruption go with option NOCHECKSUM as well as to avoid creating checksum for each block in backup. Logical block corruption is not done at the time of backup, for this to happen we have to provide command as mentioned below.

run {
configure device type disk backup type to compressed backupset parallelism 2;
backup check logical database plus archivelog;   # validate each and every block for physical
}                                                                       # as well as logical corruption

Validate database;
It would validate the whole database for physical corruption.

validate check logical database;
 It would validate the whole database for physical as well as for logical corruption.



Renaming and Relocating Oracle datafiles.

To rename a datafile.

SQL> alter tablespace xyz offline;  
                     or database would be in mount stage.
SQL> startup force mount;

If not  taken offline, datafile would be relocated but needed media recovery before it is to be accessed.
to perform media recovery first take the tablespace offline or have a hold on the database in mount stage.
SQL> alter tablespace xyz offline;
SQL> recover datafile 'C:\app\admin\oradata\xyz.dbf';
SQL> alter tablespace xyz online;

 To rename, relocate or both of a datafile in the same tablespace.

Take the tablespace oflfine or hold the database in mount stage. If we failed to hold the database in mount stage or the datafile in offline, the relocated datafile needs media recovery.
SQL> alter tablespace xyz offline;
             or
SQL> startup force mount;
copy and paste the datafile in desired location. if it is to be renamed change the name.
SQL> alter tablespace xyz rename datafile 'C:\app\admin\oradata\old\xyz.dbf', to
                                                         'C:\app\admin\oradata\old\data\abc.dbf';
SQL> alter tablespace xyz online;
              or
SQL> alter database open;   # if the database is in mount mode.


To rename, relocate or both of a datafile of different tablespace.
 Hold the database in mount stage but be sure the database should not be open, if the database is open, hold the datafiles or tablespaces  in offline mode then fire the below mentioned command.
 1. Mount the database.
SQL> startup force mount;
          or
Place both the tablespaces in offline mode.
SQL> alter tablespace xyz offline;
SQL> alter tablespace abc offline;
  2. copy and paste the datafiles in desired location. if it is to be renamed change the name of that datafile
SQL> alter database rename file
                  'C:\app\admin\oradata\old\xyz.dbf','C:\app\admin\oradata\old\ac.dbf'       to                         'C:\app\admin\oradata\new\xyz.dbf','C:\app\admin\oradata\old\abc.dbf';
SQL> alter tablespace xyz online;
SQL> alter tablespace abc online;
           or
SQL> alter database open;         # if the database is in mount mode.

Q. Why alter database rename file here ..?

To update the controlfiles regarding these multiple changes to take effect.

**  for media recovery the tablespace or datafile should be offline or database should be in mount stage.

Restore database upto a certain time/SCN/SEQUENCE--- Incomplete recovery.

 SQL> startup force mount

Logon to RMAN prompt.

run {
set until time "to_date('30/02/2012 14:25:00','DD/MM/YYYY HH24:MI:SS')";
#set until scn 1557560;      # Database would be recovered to SCN 1557559
#set until sequence 9923;   # Database would be recovered to SEQUENCE 9922
restore database;
recover database;
alter database open resetlogs;
}

Explanation of steps ---

startup force mount - It is mandatory to have a database in mount stage to perform a recovery of whole database.
set until time "to_date(30/02/2012 14:25:00','DD/MM/YYYY HH24:MI:SS')";
set until scn 1557560;
set until sequence 9923;

 whenever we use to perform incomplete recovery to a particular SCN or sequence, the database is always recovered to mentioned SCN-1 or SEQUENCE-1 same is the case with time.

eg..
run{
set until scn 1557560;
restore database;
recover database;
alter database open resetlogs;
}

Here the database would be recovered upto SCN 1557559 same happens with the sequence and with the time.

restore database; - This command is issued to replace the corrupt or damaged files from having a good backup piece or a backup copy.
recover database; - This command is issued to apply the archived or redo logs.
alter database open resetlogs; - This is issued whenever we have an incomplete recovery as it creates a new logical life of a database means a new incarnation. To get list of it enter list incarnation; on rman prompt.