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.***

Wednesday, 27 February 2013

RMAN QUICK LEARN– FOR THE BEGINNERS

Oracle Recovery Manager (RMAN) is Oracle’s preferred method or tools by which we are able to take backups and restore and recover our database. You must develop a proper backup strategy which provides maximum flexibility to Restore & Recover the DB from any kind of failure. To develop a proper backup strategy you must decide the type of requirement then after think the possible backup option. The recommended backup strategy must include the backup of all datafiles, Archivelog and spfile & controlfile autobackup. To take online or hot backups database must be in archivelog mode. You can however use RMAN to take an offline or cold backup.
Note: Selecting the backup storage media is also important consideration. If you are storing your backup on disk then it is recommended to keep an extra copy of backup at any other server.
CREATING RECOVERY CATALOG:
Oracle recommended to use separate database for RMAN catalog. Consider in below steps the database is already created:
1. Create tablespace for RMAN:
SQL> create tablespace RTBS datafile 'D:\ORACLE\ORADATA\RTBS01.DBF' size 200M
           extent management local uniform size 5M;
2. Create RMAN catalog user:
SQL> create user CATALOG identified by CATALOG default tablespace RTBS quota unlimited on RTBS;
3. Grant some privileges to RMAN user:
SQL> Grant connect, resource to CATALOG;
SQL> grant recovery_catalog_owner to CATALOG;
4. Connect into catalog database and create the catalog:
% rman catalog RMAN_USER/RMAN_PASSWORD@cat_db log=create_catalog.log
RMAN> create catalog tablespace RTBS;
RMAN> exit;
5. Connect into the target database and into the catalog database:
% rman target sys/oracle@target_db
RMAN> connect catalog RMAN_USER/RMAN_PASSWORD@cat_db
6. Connected into the both databases, register target database:
RMAN> register database;
The following list gives an overview of the commands and their uses in RMAN. For details description search the related topics of separate post on my blog: http://shahiddba.blogspot.com/
INITIALIZATION PARAMETER:
Some RMAN related database initialization parameters:
control_file_record_keep_time: Time in days to retention records in the Control File. (default: 7 days)
large_pool_size: Memory pool used for RMAN in backup/restore operations.
shared_pool_size: Memory pool used for RMAN in backup/restore operations (only if large pool is not configured).
CONNECTING RMAN
export ORACLE_SID=<database sid>  --Linux platform
set ORACLE_SID==<database sid>    --Windows platform
To connect on a target database execute RMAN.EXE then
RMAN>connect target /
RMAN>connect target username/password
RMAN>connect target username/password@target_db
To connect on a catalog database:
RMAN>connect catalog username/password
RMAN>connect catalog username/password@catalog_db
To connect directly from the command prompt:
C:\>rman target /                 --target with nocatalog
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database: RMAN (DBID=63198018)
using target database controlfile instead of recovery catalog
C:\>rman target sys/oracle@orcl3 catalog catalog/catalog@rman  --with catalog
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database: SADHAN (DBID=63198018)
connected to recovery catalog database
RMAN PARAMETERS
RMAN parameters can be set to a specified value and remain persistent. This information is stored in the target database’s controlfile (By default). Alternatively you can store this backup information into recovery catalog. If you connect without catalog or only to the target database, your repository should be in the controlfile.
SHOW/CONFIGURE – SHOW command will show current values for set parameters and CONFIGURE – Command to set new value for parameter
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO [disk|sbt]; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>show datafile backup copies;
RMAN>show default device type;
RMAN>show device type;
RMAN>show channel;
RMAN>show retention policy;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
CONFIGURE channel device type disk format 'D:\oraback\%U';
You can set many parameters by configuring them first and making them persistent or you can override them (discard any persistent configuration) by specifying them explicitly in your RMAN backup command.

Setting
Default
Recommended
Controlfile autobackup
off
on
Retention policy
to redundancy 1
to recovery window of 30 days
Device type
disk parallelism 1 ...
disk|sbt prallelism 2 ...
Default device type
to disk
to disk
Backup optimization
off
off
Channel device type
none
disk parms=‘...’
Maxsetsize
unlimited
depends on your database size
Appending CLEAR or NONE at the last of configuration parameter command will reset the configuration to default and none setting.
CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE RETENTION POLICY NONE;
Overriding the configured retention policy:
change backupset 421 keep forever nologs;
change datafilecopy 'D:\oracle\oradata\users01.dbf' keep until 'SYSDATE+30';
RMAN BACKUP SCRIPTS:
Backing up the database can be done with just a few commands or can be made with numerous options.
RMAN> backup database;
RMAN> backup as compressed backupset database;
RMAN> Backup INCREMENTAL level=0 database;
RMAN> Backup database TAG=Weekly_Sadhan;
RMAN> Backup database MAXSETSIZE=2g;
RMAN> backup TABLESPACE orafin;
You may also combine options together in a single backup and for multi channel backup.
RMAN> Backup INCREMENTAL level=1 as COMPRESSED backupset database
FORMAT 'H:\ORABACK\%U' maxsetsize 2G;
backup full
datafile x,y,z
incremental level x
include current controlfile
archivelog all 
delete [all] input
copies x
filesperset x
maxsetsize xM
diskratio x
format = 'D:\oraback\%U';
run {
allocate channel d1 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
      backup
            incremental level 0 tag Sadhan_Full_DBbackup
            filesperset 8
            FORMAT "H:\oraback\Weekly_%T_FULL_%d-%s_%p.db" DATABASE;
            SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
      backup
            archivelog all tag Sadhan_Full_Archiveback
            filesperset 8
            format "H:\oraback\Weekly_%T_FULL_%d-%s_%p.arch";
 release channel d1;
 release channel d2;
 release channel d3;
 }
The COPY command and some copy scripts:
copy datafile 'D:\oracle\oradata\users01.dbf' TO 'H:\oraback\users01.dbf' tag=DF3,
datafile 4 to TO 'H:\oraback\users04.dbf' tag=DF4,
archivelog 'arch_1060.arch' TO 'arch_1060.bak' tag=CP2ARCH16;
run {
allocate channel c1 type disk;
copy datafile 'd:\oracle\oradata\users01.dbf' TO 'h:\oraback\users01.dbf' tag=DF3,
archivelog 'arch_1060.arch' TO 'arch_1060.bak' tag=CP2ARCH16;
   }
COMPRESSED – Compresses the backup as it is taken.
INCREMENTAL – Selecting incremental allows to backup only changes since last full backup.
FORMAT – Allows you to specify an alternate location.
TAG – You can name your backup.
MAXSETSIZE – Limits backup piece size.
TABLESPACE – Allows you to backup only a tablespace.
RMAN MAINTAINANCE :
You can review your RMAN backups using the LIST command. You can use LIST with options to customize what you want RMAN to return to you.
RMAN> list backup SUMMARY;
RMAN> list ARCHIVELOG ALL;
RMAN> list backup COMPLETED before ‘02-FEB-09’;
RMAN> list backup of database TAG Weekly_sadhan;
RMAN> list backup of datafile "D:\oracle\oradata\sadhan\users01.dbf" SUMMARY;
You can test your backups using the validate command.
RMAN> list copy of tablespace "SYSTEM";
You can ask RMAN to report backup information.
RMAN> restore database validate;
RMAN> report schema;
RMAN> report need backup;
RMAN> report need backup incremental 3 database;
RMAN> report need backup days 3;
RMAN> report need backup days 3 tablespace system;
RMAN>report need backup redundancy 2;
RMAN>report need backup recovery window of 3 days;
RMAN> report unrecoverable;
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> delete noprompt obsolete;
RMAN> crosscheck;
RMAN> crosscheck backup;
RMAN> crosscheck backupset of database;
RMAN> crosscheck copy;
RMAN> delete expired;   --use this after crosscheck command
RMAN> delete noprompt expired backup of tablespace users;
To delete backup and copies:
RMAN> delete backupset 104;
RMAN> delete datafilecopy 'D:\oracle\oradata\users01.dbf';
To change the status of some backups or copies to unavailable come back to available:
RMAN>change backup of controlfile unavaliable;
RMAN>change backup of controlfile available;
RMAN>change datafilecopy 'H:\oraback\users01.dbf' unavailable;
RMAN>change copy of archivelog sequence between 230 and 240 unavailable;
To catalog or uncatalog in RMAN repository some copies of datafiles, archivelogs and controlfies made by users using OS commands:
RMAN>catalog datafilecopy 'F:\oraback\sample01.dbf';
RMAN>catalog archivelog 'E:\oracle\arch_404.arc', 'F:\oracle\arch_410.arc';
RMAN>catalog controlfilecopy 'H:\oracle\oradata\controlfile.ctl';
RMAN> change datafilecopy 'F:\oraback\sample01.dbf' uncatalog;
RMAN> change archivelog 'E:\oracle\arch_404.arc', 'E:\oracle\arch_410.arc' uncatalog;
RMAN> change controlfilecopy 'H:\oracle\oradata\controlfile.ctl' uncatalog;
RESTORING & RECOVERING WITH RMAN BACKUP
You can perform easily restore & recover operation with RMAN. Depending on the situation you can select either complete or incomplete recovery process. The complete recovery process applies all the redo or archivelog where as incomplete recovery does not apply all of the redo or archive logs. In this case of recovery, as you are not going to complete recovery of the database to the most current time, you must tell Oracle when to terminate recovery.
Note: You must open your database with resetlogs option after each incomplete recovery. The resetlogs operation starts the database with a new stream of log sequence number starting with sequence 1.
DATAFILE <filename> – Restore specified datafile.
CONTROLFILE – To restore controlfile from backup database must be in nomount.
ARCHIVELOG <logseq>  or ARCHIVELOG from <logseq> until <logseq> – Restore archivelog to location there were backed up.
TABLESPACE <tbls_name> – Restores all the datafiles associated with specified tablespace. It can be done with database open.
RECOVER TABLESPACE/DATAFILE:
If a non-system tablespace or datafile is missing or corrupted, recovery can be performed while the database remains open.
  1. STARTUP; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
  2. Use OS commands to restore the missing or corrupted datafile to its original location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf
SQL>ALTER DATABASE DATAFILE3 OFFLINE; (tablespace cannot be used because the database is not open)
SQL>ALTER DATABASE OPEN;
SQL>RECOVER DATAFILE 3;
  1. SQL>ALTER TABLESPACE <TABLESPACE_NAME> ONLINE; (Alternatively you can use ‘alter database’ command to take datafile online)
If the problem is only the single file then restore only that particualr file otherwise restore & recover whole tablespace. The database can be in use while recovering the whole tablespace.
run {
 sql alter tablespace users offline;
 allocate channel c1 device type disk|sbt;
 restore tablespace users;
 recover tablespace users;
 sql alter tablespace users online;
}
If the problem is in SYSTEM datafile or tableapce then you cannnot open the database. You need sifficient downtime to recover it. If problem is in more than one file then it is better to recover whole tablepace or database.
startup mount
run {
 allocate channel c1 device type disk|sbt;
 allocate channel c2 device type disk|sbt;
 restore database check readonly;
 recover database;
 alter database open;
}
DATABASE DISASTER RECOVERY:
Disaster recovery plans start with risk assessment. We need to identify all the risks that our data center can face such as: All datafiles are lost, All copies of current controlfile are lost, All online redolog group member are lost, Loss of OS, loss of a disk drive, complete loss of our server etc: Our disaster plan should give brief description about recovery from above disaster. Planning Disaster Recovery in advance is essential for DBA to avoid any worrying or panic situation.
The below method is used for complete disaster recovery on the same as well as different server.
set dbid=xxxxxxx
startup nomount;
run {
allocate channel c1 device type disk|sbt;
restore spfile to ‘some_location’ from autobackup;
recover database;
alter database open resetlogs;
}
shutdown immediate;
startup nomount;
run {
allocate channel c1 device type disk|sbt;
restore controlfile from autobackup;
alter database mount;
}
RMAN> restore database;
RMAN> recover database;   --no need incase of cold backup
RMAN> alter database open resetlogs;
}
DATABASE POINT IN TIME RECOVERY:
DBPITR enables you to recover a database to some time in the past. For example, if logical error occurred today at 10.00 AM, DBPITR enables you to restore the entire database to the state it was in at 09:59 AM there by removing the effect of error but also remove all other valid update that occur since 09:59 AM. DBPTIR requires the database is in archivelog mode, and existing backup of database created before the point in time to which you wish to recover must exists, and all the archivelog and online logs created from the time of backup until the point in time to which you wish to recover must exist as well.
RMAN> shutdown Abort;
RMAN> startup mount;
RMAN> run {
   Set until time to_date('12-May-2012 00:00:00′, ‘DD-MON-YYYY  HH24:MI:SS');
   restore database;
   recover database;
          }
RMAN> alter database open resetlogs;
Caution: It is highly recommended that you must backup your controlfile and online redo log file before invoking DBPITR. So you can recover back to the current point in time in case of any issue.
Oracle will automatically stop recovery when the time specified in the RECOVER command has been reached. Oracle will respond with a recovery successful message.
SCN/CHANGE BASED RECOVERY:
Change-based recovery allows the DBA to recover to a desired point of System change number (SCN). This situation is most likely to occur if archive logfiles or redo logfiles needed for recovery are lost or damaged and cannot be restored.
Steps:
        If the database is still open, shut down the database using the SHUTDOWN command with the ABORT option.
        Make a full backup of the database including all datafiles, a control file, and the parameter files in case an error is made during the recovery.
        Restore backups of all datafiles. Make sure the backups were taken before the point in time you are going to recover to. Any datafiles added after the point in time you are recovering to should not be restored. They will not be used in the recovery and will have to be recreated after recovery is complete. Any data in the datafiles created after the point of recovery will be lost.
        Make sure read-only tablespace are offline before you start recovery so recovery does not try to update the datafile headers.
RMAN> shutdown Abort;
RMAN> startup mount;
RMAN>run {
  set until SCN 1048438;
  restore database;
  recover database;
  alter database open resetlogs;
   }
RMAN> restore database until sequence 9923; --Archived log sequence number
RMAN> recover database until sequence 9923; --Archived log sequence number
RMAN> alter database open resetlogs;
Note: Query with V$LOG_HISTORY and check the alert.log to find the SCN of an event and recover to a prior SCN.
IMPORTANT VIEW:
Views to consult into the target database:
v$backup_device: Device types accepted for backups by RMAN.
v$archived_log:  Redo logs archived.
v$backup_corruption: Corrupted blocks in backups.
v$copy_corruption: Corrupted blocks in copies.
v$database_block_corruption: Corrupted blocks in the database after last backup.
v$backup_datafile: Backups of datafiles.
v$backup_redolog: Backups of redo logs.
v$backup_set: Backup sets made.
v$backup_piece: Pieces of previous backup sets made.
v$session_long_ops: Long operations running at this time.
Views to consult into the RMAN catalog database:
rc_database: Information about the target database.
rc_datafile: Information about the datafiles of target database.
rc_tablespace: Information about the tablespaces of target database.
rc_stored_script: Stored scripts.
rc_stored_script_line: Source of stored scripts.
For More Information on RMAN click on the below link:
24-Feb-13
16-Feb-13
27-Jan-13
25-Jan-13
24-Jan-13
23-Jan-13
24-Nov-12
4-Nov-12
14-Oct-12
7-Oct-12
3-Sep-12
26-Aug-12
22-Aug-12
Script: RMAN Hot Backup on Linux Environment
1-Aug-12
31-Jul-12
7-Jul-12
2-Jun-12
16-May-12
15-May-12
12-May-12
12-May-12
6-May-12
30-Apr-12
30-Apr-12
23-Apr-12
22-Apr-12
18-Apr-12
11-Apr-12
7-Apr-12
25-Mar-12
6-Mar-12

4 comments:

  1. In case you are looking into earning money from your websites by popup advertisments, you should try one of the biggest companies - Ero-Advertising.

    ReplyDelete
  2. Thanks for sharing useful information for the beginners and good points were mentioned in the blog

    http://www.calfre.com/Hyderabad/Oracle-Fusion-Financials-Online-Training/listing?cod=111631

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Thanks for sharing the info. Please visit my blogs for more insights on database.
    https://snapshottooold.blogspot.com
    http://snapshottooold.wordpress.com/

    ReplyDelete