Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 16 February 2013

Synchronizes the Test database with RMAN Cold Backup

If the recovery catalog is maintained in the controlfile and that, prior to the restore, the controlfile has not been damaged, so the recovery catalog information it contains is fully intact. Thus RMAN can determine from its recovery catalog whether the backup was made using a DISK or SBT_TAPE channel and will automatically allocate a channel of the correct type for reading from the backup file. If the controlfile had been damaged, it would be necessary to use an additional step to restore the controlfile from the backup, and a DISK channel should be explicitly allocated for that step of the restore in an RMAN run block, in order to assure RMAN uses the correct device type to read the controlfile backup.
This example shows if you want to restore Test environment with the backup of production database. Configure RMAN; assume Fast Recovery Area (FRA) is used, so no ‘format’ parameters needed.
RMAN>Configure Controlfile autobackup ON;
RMAN>Configure default device type to disk;
Now Perform the RMAN Cold backup of Production database, while the database is in mount state, no matter the database is in Archive log or Noarchivelog . You can also perform Shutdown and startup from the RMAN prompt or use the below script for the RMAN_COLD backup. Below is the two .cmd file, one to connect the RMAN with target database and other to perform backup.
// Create objects to mark the restore point before backup
CREATE TABLE SHAHID
(
NAME VARCHAR2(20),
TODAY_DATE DATE
);
INSERT INTO SHAHID VALUES (‘SHAHID’, ‘12-FEB-2013’);
Sadhan_Cold_Backup.cmd
D:\oracle\ora92\bin\RMAN TARGET SYS/****@***** NOCATALOG CMDFILE=D:\Cold_Backup\Sadhan_Cold_Backup\Sadhan_Cold_Backup.rcv LOG=D:\Cold_Backup\Sadhan_Cold_Backup\Logs\Sadhan_Cold_Backup.LOG
Sadhan_Cold_Backup.rcv
# SADHAN Full RMAN Cold or consistent Backup Script
run
{
shutdown immediate;
startup mount;
allocate channel d1 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db";

backup full format "H:\oraback\SADHAN_COLD_%T_%d-%s_%p.db" TAG= "SADHAN_COLD_BACKUP" (database);
copy current controlfile to 'H:\oraback\SADHAN_COLD_Control.db';

release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;

alter database open;
}
Caution: Make sure RMAN is not connected to recovery Catalog; otherwise RMAN records information about restored database in recovery catalog as the DBID is same. Thus you are not able to restore and recover the database in future with this RMAN.

Now Copy these backup on to the destination host. If you did not copy the backup on same drive structure and backup folder on to destination server then you need to catalog the backup folder.
To be able to restore from a backup, RMAN must be able to display the backup in its list of backups which can be obtained by typing “List backupset” at the RMAN command prompt. For this example, RMAN stores its backup list information in the controlfile. If we didn’t restore the controlfile itself from the backup first, and this were an old backup, we might have to take steps to update RMAN’s recorded status of the backup to “available,” or even to add the backup to the recovery catalog in the controlfile with RMAN’s “catalog” command. However, since we are restoring the controlfile with the backup, we are actually restoring the recovery catalog to its original state along with the datafile, and since we restore the controlfile first and know the backup will be the last backup in the controlfile after the controlfile is restored, we don’t have to worry the backup’s status in the catalog.
This example is just to synchronizes the Test environment DB on new server(Parameter file is same as previous). If you want to perform complete disaster recovery on new server then you need to restore spfile first with autobackup.
// Startup DB in Nomount and restore Controlfile.
SQL> startup nomount;
C:>rman  (i.e. start RMAN from the command line with no parameters)
RMAN> set dbid=163198018;
executing command: SET DBID
RMAN> connect target sys/oracle@sadhan.world;
connected to target database (not started)
Note: Since the database is in nomount (controlfile is not started), you are not able to use show all and list backup command. Try to make sure the backup or use the above command after restoring the controlfile.
RMAN> restore controlfile from autobackup;
-or-
RMAN> run
      {
set controlfile autobackup format for device type disk to
'H:\ORABACK\%F';
      RESTORE controlfile FROM AUTOBACKUP;
      }
Starting restore at 08-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: H:\oraback
database name (or database unique name) used for search: SADHAN
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20130211
channel ORA_DISK_1: autobackup found: H:\ORABACK\C-63198018-20130211-04
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\ORADATA\SADHAN\CONTROL01.CTL
output filename=D:\ORACLE\ORADATA\SADHAN\CONTROL02.CTL
output filename=D:\ORACLE\ORADATA\SADHAN\CONTROL03.CTL
Finished restore at 11-FEB-13
// Mount the database and run the restore command.
RMAN> alter database mount;
RMAN> restore database;

Starting restore at 11-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
restoring datafile 00009 to G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\SADHAN_COLD_20130211_SADHAN-5006_1.DB tag=SADHAN_COLD_BA
CKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
restoring datafile 00011 to G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
restoring datafile 00012 to G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\SADHAN_COLD_20130211_SADHAN-5008_1.DB tag=SADHAN_COLD_BA
CKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\SADHAN_COLD_20130211_SADHAN-5009_1.DB tag=SADHAN_COLD_BA
CKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
restoring datafile 00010 to F:\ORACLE\SADHAN\SDH_EDSS01.DBF
restoring datafile 00014 to D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\SADHAN_COLD_20130211_SADHAN-5007_1.DB tag=SADHAN_COLD_BA
CKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
restoring datafile 00013 to F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\SADHAN_COLD_20130211_SADHAN-5010_1.DB tag=SADHAN_COLD_BA
CKUP params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 11-FEB-13

RMAN> alter database open resetlogs;
database opened
The “resetlogs” is needed when opening the database at this point. But once that command is issued, the same instance of the database cannot be restored again from old backups. Thus, it is important to perform another backup of the database immediately after this line has successfully been executed.
SQL> shutdown immediate;
SQL> startup mount;
C:>rman target /
RMAN> backup database;
// Create Tempfile and Validate the success of Restore
Check the restore point to validate the success of restore and check the Tempfile (must in case of new server restore). If the tempfile is not available create the new tempfile.
SELECT FILE_NAME FROM DBA_DATA_FILES, DBA_TABLESPACES WHERE
DBA_DATA_FILES.TABLESPACE_NAME = DBA_TABLESPACES.TABLESPACE_NAME AND DBA_TABLESPACES.CONTENTS = 'TEMPORARY';

SELECT * FROM DBA_TEMP_FILES;

ALTER TABLESPACE TEMP
ADD TEMPFILE 'D:\ORACLE\ORADATA\sadhan\TEMP02.DBF' SIZE 15000M
AUTOEXTEND ON NEXT 5M MAXSIZE 30000M;

SELECT INSTANCE_NAME FROM V$INSTANCE;
SELECT DATABASE_NAME FROM V$DATABASE;
SELECT * FROM GLOBAL_NAME;
SELECT * FROM DBA_DATA_FILES;
SELECT * FROM V$LOGFILE;
SELECT * FROM V$LOG;
SELECT LOG_MODE FROM V$DATABASE;
SELECT * FROM HRMS.PAY_PAYMENT_MASTER ORDER BY PAYSLIP_DATE;
SELECT * FROM V$CONTROLFILE;
SELECT * FROM DATABASE_PROPERTIES;

0 comments:

Post a Comment