RMAN Backup & Recovery
The RMAN ambiance consists of the utilities and databases that comedy a role in abutment up your data. At a minimum, the ambiance for RMAN charge accommodate the following:
The ambition database to be backed up
The RMAN client, which interprets advancement and accretion commands, directs server sessions to assassinate those commands, and anneal your advancement and accretion action in the ambition database ascendancy file.
Logical Backup : backup utility that performs backups on the logical components of the database. A logical backup consists of backing up the database at the tablespace level or backing up other logical database components such as a table.
Physical Backup: Backing up the database files such as the datafiles, control files, and redo log file
RMAN Repository and Control Files
The RMAN utility uses two methods of storing information about the target databases that are backed up. Oracle calls the collection of metadata about the target databases the RMAN repository There are two methods of storing data in the RMAN repository.
Recovery Catalog
The first method is by accessing an RMAN catalog of information about backups. The second method is by accessing the necessary information about backups in the target database’s control files. The init.ora or spfile.ora parameter CONTROL_FILE_RECORD_KEEP_TIME determines how long information that can be used by RMAN is kept in the control file. The default value for this parameter is 7 days and can be as many as 365 days.
The recovery catalog is designed to be a central storage place for multiple databases’ RMAN information. This centralizes the location of the RMAN information instead of having this information dispersed in each target database’s control file. Here is a list of the components contained in a recovery catalog:
Backup and recovery information that is logged for long-term use from the target databases
RMAN scripts that can be stored and reused
Backup information about datafiles and archive logs
Information about the physical makeup, or schema, of the target database
RMAN (Configuration ) Using Recovery Catalog
Creating user for RMAN, grant required resources/privileges and register database
Note: here i am not utilizing defalt rman backup location (db_recovery_dest), i will create new directory named "rman" for holding all rman backups.
Datebase : Oracle 10g, Oracle 11g
1. Login as root user, create directory and permit to rman user for reading/writing backups on directory
[root@redhat ~]# chown -R oracle:oinstall /rman
[root@redhat ~]# chmod -R 775 /rman
2. Login to database , create rman user and grant necessary privileges
[root@redhat ~]# su - oracle
[oracle@redhat ~]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jun 1 18:35:40 2004
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> create user rman identified by rman
2 default tablespace users
3 temporary tablespace temp;
User created.
SQL> grant connect, resource, recovery_catalog_owner to rman;
Grant succeeded.
3. Connect to RMAN, create catalog and register database.
[oracle@redhat ~]$ rman
Recovery Manager: Release 11.1.0.6.0 - Production on Tue Jun 1 18:38:55 2004
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target
connected to target database: ORCL (DBID=1232238763)
RMAN> connect catalog rman/rman@orcl
connected to recovery catalog database
RMAN> create catalog tablespace users;
recovery catalog created
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
4. View default RMAN parameters and change if required
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oradisk/oracle/product/11.1.0/db/dbs/snapcf_ORCL.f'; # default
Note: here i am changing default parameters - (you can leave it, if want to utilize default )
RMAN> configure device type disk backup type to compressed backupset;
new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete
RMAN> configure retention policy to recovery window of 30 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure controlfile autobackup format for device type disk to '/rman/%F';
new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/%F'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete
Creating Backup Sets
RMAN> connect target
Full Database Backup (archive logs not included)
RMAN> BACKUP DATABASE;
Note: we can also take backup as following:
RMAN> run
{ allocate channecl c1 type disk;
backup database format '/rman/%d_%I_%s_%T';
}
RMAN>
Database and Archive log Backup
RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s';
backup format 'log_t%t_s%s_p%p' (archivelog all);
}
RMAN>
Note: can user following parameters if required--BACKUP (ARCHIVELOG ALL DELETE INPUT);
Using the TAG command to name the backup monthly backup.
RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s' tag monthly_backup;
backup format 'log_t%t_s%s_p%p' (archivelog all);
}
we can create rman script, and execute when required
CREATE SCRIPT wholedbbackup
COMMENT "Whole database backup and archive log"
{ ALLOCATE CHANNEL C1 TYPE DISK;
ALLOCATE CHANNEL C2 TYPE DISK;
BACKUP DATABASE [backup as compressed backupset database TAB COMPRESED_DBBK_082807];
BACKUP FORMAT 'ARC%S_%R.%T' (ARCHIVELOG ALL); }
RUN { EXECUTE script wholedbbackup; }
Backup the control file:
RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';
Backup tablespace:
RMAN> BACKUP TABLESPACE system, users, tools;
Backup datafiles :
RMAN> BACKUP AS BACKUPSET DATAFILE 'ORACLE_HOME/oradata/trgt/users01.dbf',
'ORACLE_HOME/oradata/trgt/tools01.dbf';
Backup parameter file:
RMAN> BACKUP SPFILE;
Backup Everything Using Backupset Command:
RMAN> BACKUP BACKUPSET ALL;
Database Recovery from RMAN backups (Examples)
Database recovery depend on events, it may requird full database recover or single database file, redolog , controlfile even spfile (parameter file), here some examples for database recover using RMAN backup.
Restore spfile from backup
RMAN> CONNECT TARGET;
RMAN> SET DBID 1232238763
RMAN> STARTUP FORCE NOMOUNT
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
Restoring and recovering a datafile
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7ONLINE';
Restore and recover a tablespace
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';
Restore and recover the whole database
RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;
Monitoring RMAN Backups
Connect database and find databas dbid
SQL> connect system/manager@orcl
SQL> select dbid from v$database;
SQL> connect rman_user/rman_user@orcl
SQL> select db_key from rc_database where dbid = 1232238763
SQL> select bs_key, backup_type, completion_time
from rc_database_incarnation a, rc_backup_set b
where a.db_key = b.db_key
and a.db_key = b.db_key
and a.current_incarnation = ‘YES’;
RMAN Reporting
RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> list backupset by file;
RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> REPORT OBSOLETE;
RMAN> REPORT SCHEMA;
The RMAN ambiance consists of the utilities and databases that comedy a role in abutment up your data. At a minimum, the ambiance for RMAN charge accommodate the following:
The ambition database to be backed up
The RMAN client, which interprets advancement and accretion commands, directs server sessions to assassinate those commands, and anneal your advancement and accretion action in the ambition database ascendancy file.
Logical Backup : backup utility that performs backups on the logical components of the database. A logical backup consists of backing up the database at the tablespace level or backing up other logical database components such as a table.
Physical Backup: Backing up the database files such as the datafiles, control files, and redo log file
RMAN Repository and Control Files
The RMAN utility uses two methods of storing information about the target databases that are backed up. Oracle calls the collection of metadata about the target databases the RMAN repository There are two methods of storing data in the RMAN repository.
Recovery Catalog
The first method is by accessing an RMAN catalog of information about backups. The second method is by accessing the necessary information about backups in the target database’s control files. The init.ora or spfile.ora parameter CONTROL_FILE_RECORD_KEEP_TIME determines how long information that can be used by RMAN is kept in the control file. The default value for this parameter is 7 days and can be as many as 365 days.
The recovery catalog is designed to be a central storage place for multiple databases’ RMAN information. This centralizes the location of the RMAN information instead of having this information dispersed in each target database’s control file. Here is a list of the components contained in a recovery catalog:
Backup and recovery information that is logged for long-term use from the target databases
RMAN scripts that can be stored and reused
Backup information about datafiles and archive logs
Information about the physical makeup, or schema, of the target database
RMAN (Configuration ) Using Recovery Catalog
Creating user for RMAN, grant required resources/privileges and register database
Note: here i am not utilizing defalt rman backup location (db_recovery_dest), i will create new directory named "rman" for holding all rman backups.
Datebase : Oracle 10g, Oracle 11g
1. Login as root user, create directory and permit to rman user for reading/writing backups on directory
[root@redhat ~]# chown -R oracle:oinstall /rman
[root@redhat ~]# chmod -R 775 /rman
2. Login to database , create rman user and grant necessary privileges
[root@redhat ~]# su - oracle
[oracle@redhat ~]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jun 1 18:35:40 2004
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> create user rman identified by rman
2 default tablespace users
3 temporary tablespace temp;
User created.
SQL> grant connect, resource, recovery_catalog_owner to rman;
Grant succeeded.
3. Connect to RMAN, create catalog and register database.
[oracle@redhat ~]$ rman
Recovery Manager: Release 11.1.0.6.0 - Production on Tue Jun 1 18:38:55 2004
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target
connected to target database: ORCL (DBID=1232238763)
RMAN> connect catalog rman/rman@orcl
connected to recovery catalog database
RMAN> create catalog tablespace users;
recovery catalog created
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
4. View default RMAN parameters and change if required
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oradisk/oracle/product/11.1.0/db/dbs/snapcf_ORCL.f'; # default
Note: here i am changing default parameters - (you can leave it, if want to utilize default )
RMAN> configure device type disk backup type to compressed backupset;
new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete
RMAN> configure retention policy to recovery window of 30 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure controlfile autobackup format for device type disk to '/rman/%F';
new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/%F'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete
Creating Backup Sets
RMAN> connect target
Full Database Backup (archive logs not included)
RMAN> BACKUP DATABASE;
Note: we can also take backup as following:
RMAN> run
{ allocate channecl c1 type disk;
backup database format '/rman/%d_%I_%s_%T';
}
RMAN>
Database and Archive log Backup
RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s';
backup format 'log_t%t_s%s_p%p' (archivelog all);
}
RMAN>
Note: can user following parameters if required--BACKUP (ARCHIVELOG ALL DELETE INPUT);
Using the TAG command to name the backup monthly backup.
RMAN> run
{ allocate channel c1 type disk;
backup database format 'db_%u_%d_%s' tag monthly_backup;
backup format 'log_t%t_s%s_p%p' (archivelog all);
}
we can create rman script, and execute when required
CREATE SCRIPT wholedbbackup
COMMENT "Whole database backup and archive log"
{ ALLOCATE CHANNEL C1 TYPE DISK;
ALLOCATE CHANNEL C2 TYPE DISK;
BACKUP DATABASE [backup as compressed backupset database TAB COMPRESED_DBBK_082807];
BACKUP FORMAT 'ARC%S_%R.%T' (ARCHIVELOG ALL); }
RUN { EXECUTE script wholedbbackup; }
Backup the control file:
RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';
Backup tablespace:
RMAN> BACKUP TABLESPACE system, users, tools;
Backup datafiles :
RMAN> BACKUP AS BACKUPSET DATAFILE 'ORACLE_HOME/oradata/trgt/users01.dbf',
'ORACLE_HOME/oradata/trgt/tools01.dbf';
Backup parameter file:
RMAN> BACKUP SPFILE;
Backup Everything Using Backupset Command:
RMAN> BACKUP BACKUPSET ALL;
Database Recovery from RMAN backups (Examples)
Database recovery depend on events, it may requird full database recover or single database file, redolog , controlfile even spfile (parameter file), here some examples for database recover using RMAN backup.
Restore spfile from backup
RMAN> CONNECT TARGET;
RMAN> SET DBID 1232238763
RMAN> STARTUP FORCE NOMOUNT
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
Restoring and recovering a datafile
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7ONLINE';
Restore and recover a tablespace
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';
Restore and recover the whole database
RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;
Monitoring RMAN Backups
Connect database and find databas dbid
SQL> connect system/manager@orcl
SQL> select dbid from v$database;
SQL> connect rman_user/rman_user@orcl
SQL> select db_key from rc_database where dbid = 1232238763
SQL> select bs_key, backup_type, completion_time
from rc_database_incarnation a, rc_backup_set b
where a.db_key = b.db_key
and a.db_key = b.db_key
and a.current_incarnation = ‘YES’;
RMAN Reporting
RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> list backupset by file;
RMAN> LIST BACKUPSET BY BACKUP SUMMARY;
RMAN> REPORT OBSOLETE;
RMAN> REPORT SCHEMA;
No comments:
Post a Comment