Archivelog and Noarchivelog Modes:
Oracle writes all changes to the data blocks in memory to the online redo logs, usually before they are written to the database files. During a recovery process, Oracle uses the changes recorded in the redo log files to bring the database up-to-date. Oracle can manage the redo log files in two ways:
Archivelog mode: In this mode, Oracle saves (archives) the filled redo logs. Thus, no matter how old the database backup is, if you are running in archivelog mode, you can recover the database to any point in time using the archived logs. In this mode we can perform open backups—that is, backups while the database is running—only if the database is operating in archivelog mode.
Noarchivelog mode: In this mode, the filled redo logs are overwritten and not saved.In this mode thus implies that you can restore only the backup, and you’ll lose all the changes made to the database after the backup was performed.
Oracle backups is divided into logical and physical backups.
Logical Backup:
Logical backups are backups made using the Data Pump Export utility.
Physical backups refer to the backing up of the key Oracle database files: data files, archivedredo logs, and control files. Physical backups are made on disk or on tape drives.
Backup Levels:
Whole database:Back up all files including the control file. This level is applicable to both archivelog and noarchivelog modes of operation.
Tablespace backups:Back up all the data files belonging to a tablespace. Tablespace backups are applicable only in the archivelog mode.
Data file backups:Back up a single data file. Data file backups are valid in the archivelogmode only.
RMAN backups classification:
Full, Partial or incremental:
Open or closed:
Online or open (or hot/warm) backups are backups you make while the database is open and accessible to users.
Online backup not possible if the database is running in noarchivelog mode.
Closed (or clod) backup is made while the database is shut down. A closed backup is always consistent, as long as the database wasn’t shut down with the SHUTDOWN ABORT.Closed backups can be consistent or inconsistent.
Consistent or inconsistent:
Consistent:
consistent database means that the SCNs stored in all the data file headers are identical and are also the same as the data file header information held in the control files. The important thing is that the same SCN number must appear in all the data files and the control file(s).
Since the data is consistent, you don’t need to perform any recovery steps after you restore (or copy back) a set of backup files.
To make a consistent backup, either the database needs to be closed (with a normal SHUTDOWN
or SHUTDOWN TRANSACTIONAL command, not a SHUTDOWN ABORT command) or it needs to be in a mount position after being started (again, after a clean shutdown).
Inconsistent:
An inconsistent backup is a backup in which the files contain data from different points in
time. Inconsistent backups don’t mean there is anything wrong with your backups.However, during a recovery process, it isn’t sufficient to merely restore these backups. In addition to restoring these backups, you must also supply all archived and online redo logs from the time of the backup to the time to which you want to recover the database.
CHANNEL:
A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files.
OBSOLETE:
If a backup item is no longer needed for recovery – because it is older than the retention policy – then it is obsolete. A backup is obsolete if it’s no longer needed for database recovery, according to your retention policy.
DELETE OBSOLETE command will remove all backups you no longer need.
EXPIRED:
An expired backup means that the backup file can’t be found by RMAN but backup info holds in controlfile or catalog.
Run the CROSSCHECK command so RMAN can mark the backups it can’t find as expired and then use the DELETE EXPIRED command to remove the records for these files from the control file and the recovery catalog.
DELETE EXPIRED command removes the recovery catalog records for expired backups and marks them as DELETED.
Oracle writes all changes to the data blocks in memory to the online redo logs, usually before they are written to the database files. During a recovery process, Oracle uses the changes recorded in the redo log files to bring the database up-to-date. Oracle can manage the redo log files in two ways:
Archivelog mode: In this mode, Oracle saves (archives) the filled redo logs. Thus, no matter how old the database backup is, if you are running in archivelog mode, you can recover the database to any point in time using the archived logs. In this mode we can perform open backups—that is, backups while the database is running—only if the database is operating in archivelog mode.
Noarchivelog mode: In this mode, the filled redo logs are overwritten and not saved.In this mode thus implies that you can restore only the backup, and you’ll lose all the changes made to the database after the backup was performed.
Oracle backups is divided into logical and physical backups.
Logical Backup:
Logical backups are backups made using the Data Pump Export utility.
Physical backups refer to the backing up of the key Oracle database files: data files, archivedredo logs, and control files. Physical backups are made on disk or on tape drives.
Backup Levels:
Whole database:Back up all files including the control file. This level is applicable to both archivelog and noarchivelog modes of operation.
Tablespace backups:Back up all the data files belonging to a tablespace. Tablespace backups are applicable only in the archivelog mode.
Data file backups:Back up a single data file. Data file backups are valid in the archivelogmode only.
RMAN backups classification:
Full, Partial or incremental:
Open or closed:
Online or open (or hot/warm) backups are backups you make while the database is open and accessible to users.
Online backup not possible if the database is running in noarchivelog mode.
Closed (or clod) backup is made while the database is shut down. A closed backup is always consistent, as long as the database wasn’t shut down with the SHUTDOWN ABORT.Closed backups can be consistent or inconsistent.
Consistent or inconsistent:
Consistent:
consistent database means that the SCNs stored in all the data file headers are identical and are also the same as the data file header information held in the control files. The important thing is that the same SCN number must appear in all the data files and the control file(s).
Since the data is consistent, you don’t need to perform any recovery steps after you restore (or copy back) a set of backup files.
To make a consistent backup, either the database needs to be closed (with a normal SHUTDOWN
or SHUTDOWN TRANSACTIONAL command, not a SHUTDOWN ABORT command) or it needs to be in a mount position after being started (again, after a clean shutdown).
Inconsistent:
An inconsistent backup is a backup in which the files contain data from different points in
time. Inconsistent backups don’t mean there is anything wrong with your backups.However, during a recovery process, it isn’t sufficient to merely restore these backups. In addition to restoring these backups, you must also supply all archived and online redo logs from the time of the backup to the time to which you want to recover the database.
CHANNEL:
A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files.
If a backup item is no longer needed for recovery – because it is older than the retention policy – then it is obsolete. A backup is obsolete if it’s no longer needed for database recovery, according to your retention policy.
DELETE OBSOLETE command will remove all backups you no longer need.
EXPIRED:
An expired backup means that the backup file can’t be found by RMAN but backup info holds in controlfile or catalog.
Run the CROSSCHECK command so RMAN can mark the backups it can’t find as expired and then use the DELETE EXPIRED command to remove the records for these files from the control file and the recovery catalog.
DELETE EXPIRED command removes the recovery catalog records for expired backups and marks them as DELETED.
What is the difference between expired and obsolete backup?
Expire mean, the backup piece is not available at the physical location .
Obsolete backup mean, That backup piece is not required any more.
What is the difference between validate and crosscheck command?
Validate backup set – checks whether backup sets can be restored or not.
Crosscheck – go through the headers of the specified file, to check if they are on disk or tape.
Why are backups of a previous incarnation marked obsolete without regard to redundancy?
Can we restore a database from obsolete backup?
Yes we can restore a database from obsolete backup. For that we need to catalog those file explictly.
CATALOG START WITH command
RMAN> CATALOG START WITH '/disk1/backups/';
If you plan a database point in time recovery , you simply go
RMAN> run{set until time <time you like to go>; restore database;recover database;
RMAN> alter database open resetlogs;}
RMAN will then automatically use your obselete backup if needed . Obsolete does not mean expired. There is most likely no need to again catalog that backup.
Why are backups of a previous incarnation marked obsolete without regard to redundancy?
The incarnation is set to prevent you to restore backups that are too old. This way they are obsolete.
You can however still use them to restore but in that case you need to change the incarnation yourself to the 'previous' incarnation. At the same time your latest backups become obsolete.
Doc ID 206862.1 (How does the RMAN Retention Policy Obsolete Incremental Backupsets and Archivelog Files)
What is the purpose of resync catalog command?
Resynchronizing the Recovery Catalog and CONTROL_FILE_RECORD_KEEP_TIME
If you maintain a recovery catalog, then use the RMAN RESYNC CATALOG command often enough to ensure that control file records are propagated to the recovery catalog before they are reused.
Make sure that CONTROL_FILE_RECORD_KEEP_TIME is longer than the interval between backups or resynchronizations. Otherwise, control file records could be reused before they are propagated to the recovery catalog. An extra week is a safe margin in most circumstances.
What is the difference between maxpiece size and maxset size?
MAXPIECESIZE – Limits the size of each backup piece.
MAXSETSIZE – Maximum size of a backup set. Please remember, the maxsetsize value should be larger than the size of your largest data file.
Can i take the fullbackup from primary and incremental backup from standby?
Yes we can take backup from standby and primary database, as both the databases are same and have the same dbid.
Currently we have 30 days of archive logs present , so used delete archivelog all completed before ‘sysdate-3’ to delete files older than 3 days. But this command is not deleting files older than 14 days. Why?
You need to check your CONTROL_FILE_KEEP_RECROD_TIME. If this is set 14 days. then Only 14 days archive information will be stored in the control_file. If archive logs are older than 14 days, its related information will be removed from control_file.
So when we use rman it gets data from controlfile. As only 14 days information is present in control_file. RMAN delete command will ignore files older than 14 days.
Default value of CONTROL_FILE_KEEP_RECORD_TIME is 7 days.
Explain how rman works internally , when you are running rman database backup?
Lets say you connected as rman to target db and run backup database ;
rman target /
RMAN> backup database;
RMAN makes the bequeath connection with the target database.
It then connect to internal database user sys.RMAN and spawn multiple channel as mentioned in script.
Then RMAN make a call to sys.dbms_rcvman to request database schema information from controlfile( like datafile info,scn)
After getting the datafile list, it prepare for backup.To guarantee consistency it either builds or refreshes the snapshot control file.
Now RMAN make a call to sys.dbms_backup_restore package to create backup pieces.
If controlfile autobackup is set to on, then it will take backup of spfile and controfile to backupset.
During backup, datafile blocks are read into a set of input buffers, where they are validated/compressed/encrypted and copied to a set of output buffers. The output buffers are then written to backup pieces on either disk or tape (DEVICE TYPE DISK or SBT).
What are the different phases of RMAN backup.?
Read PhaseA channel reads blocks from disk into input I/O buffers.
Copy PhaseA channel copies blocks from input buffers to output buffers and performs additional processing on the blocks.
Write PhaseA channel writes the blocks from output buffers to storage media. The write phase can take either of the following mutually exclusive forms, depending on the type of backup media:
Who updates the block change tracking file(BCT) and how it does?
CTWR background process updates the block change tracking file.
REPORT SCHEMA command lists all data files that are part of the target database.
REPORT OBSOLETE command displays all the backups rendered obsolete based on the retention
policy.If there are obsolete backups in the repository, you can delete them with the DELETE OBSOLETE command.
REPORT NEED BACKUP command lists any data files that need backup to conform with the
retention policy you originally chose for your backups.
REPORT UNRECOVERABLE command lists all unrecoverable data files. An unrecoverable file
is a data file with a segment that has undergone a nologging operation, and should therefore be
backed up immediately.
LIST:
LIST BACKUP command shows you all the completed backups registered by RMAN. The
command shows all backup sets and image copies, as well as the individual data files, control files,
archived redo log files, and SPFILEs in the backup files. You can also list all backups by querying
V$BACKUP_FILES and the RC_BACKUP_FILES recovery catalog view.
LIST COPY command is analogous to the LIST BACKUP command and shows you the complete
list of all the copies made using RMAN.
LIST ARCHIVELOG ALL command will list all available archived log copies.
LIST SCRIPT NAMES command to display names of all the stored scripts
in the recovery catalog. The LIST GLOBAL SCRIPT NAMES command will show all the global scripts.
Difference b/w report and list :
Reporting list of files which need backup. RMAN Report command can be used to get a list of those datafiles which have not been backed up since last <n> days or to know which backupsets have become obsolete and can be deleted as per configured retention policy.
Example:
To know which files need backup since they have not been backed up since last 30 days give the following command
RMAN> report need backup days 2 database;
If you have configured the Retention Policy to redundancy 1. Then to know which backups have become obsolete and can be safely deleted, you can give a command as follows
RMAN> report obsolete
To know which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path INSERT give the following command
RMAN> report unrecoverable;
List:You can view the information about backups you have taken using RMAN.
To view summary of database backups
RMAN> list backup summary;
To list details information about backupsets of database
RMAN> list backupset of database;
To list information about backupset of particular tablespace, you can give the following command
RMAN> list backupset of tablespace users;
To list information about particular datafile :
RMAN> list backup of datafile 4;
To list backup set by tag
LIST BACKUPSET TAG 'myfulldbbackup';
To list backup or copy by device type
LIST COPY OF DATAFILE '/u02/oracle/test/system01.dbf' DEVICE TYPE sbt;
To list backup by directory or path
LIST COPY LIKE '/u02/oracle/flash/%';
To list a backup or copy by a range of completion dates
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '01-OCT-2016' AND '31-OCT-2016';
Difference b/w report and list :
Reporting list of files which need backup. RMAN Report command can be used to get a list of those datafiles which have not been backed up since last <n> days or to know which backupsets have become obsolete and can be deleted as per configured retention policy.
Example:
To know which files need backup since they have not been backed up since last 30 days give the following command
RMAN> report need backup days 2 database;
If you have configured the Retention Policy to redundancy 1. Then to know which backups have become obsolete and can be safely deleted, you can give a command as follows
RMAN> report obsolete
To know which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path INSERT give the following command
RMAN> report unrecoverable;
List:You can view the information about backups you have taken using RMAN.
To view summary of database backups
RMAN> list backup summary;
To list details information about backupsets of database
RMAN> list backupset of database;
To list information about backupset of particular tablespace, you can give the following command
RMAN> list backupset of tablespace users;
To list information about particular datafile :
RMAN> list backup of datafile 4;
To list backup set by tag
LIST BACKUPSET TAG 'myfulldbbackup';
To list backup or copy by device type
LIST COPY OF DATAFILE '/u02/oracle/test/system01.dbf' DEVICE TYPE sbt;
To list backup by directory or path
LIST COPY LIKE '/u02/oracle/flash/%';
To list a backup or copy by a range of completion dates
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '01-OCT-2016' AND '31-OCT-2016';
VALIDATE:
VALIDATE BACKUPSET command to validate backup sets before you use them from a
recovery.
RMAN Configuration:
SHOW ALL command to see the default values.
SELECT * FROM v$rman_configuration;
Backup Retention Policy:
A backup retention policy tells RMAN when to consider backups of data files and log files obsolete.RMAN only marks the file obsolete—it doesn’t delete it.
The REDUNDANCY Option:
The REDUNDANCY option lets you specify how many copies of the backups you want to retain.default is 1.
The RECOVERY WINDOW Option:
RECOVERY WINDOW option enables you to specify how far back in time you want to recover from when your database is affected by a media failure.
Default Device Type:
The default device for backups is disk;configure the default device type to sbt.
Degree of Parallelism:
The degree of parallelism (the default degree is 1) denotes the number of channels that RMAN can
open during a backup or recovery.
Connect to the catalog database and issue following query.
SQL> select name,dbid from rc_database;
What is the difference between physical and logical backups?
What is the significance of incarnation and DBID in the RMAN backups?
DBID stands for database identifier, which is a unique identifier for each oracle database running. It is found in control files as well as datafile header. If the database is open you can directly querying with the v$database and find the DBID. The DBID, which is a unique system-defined number given to every Oracle database, is what distinguishes one target database from another target database in the RMAN metadata.
SELECT DBID FROM V$DATABASE;
SELECT DB_KEY FROM RC_DATABASE WHERE DBID = dbid_of_target;
To obtain information about the current incarnation of a target database:
SELECT BS_KEY, BACKUP_TYPE, COMPLETION_TIME
FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b
WHERE i.DB_KEY = 1
AND i.DB_KEY = b.DB_KEY
AND i.CURRENT_INCARNATION = 'YES';
When do you recommend hot backup? What are the pre-reqs?
When there is no down time for database (24/7) ,we should go for hot backup.
pre-req:database must be in archive log mode
How do you identify the expired, active, obsolete backups? Which RMAN command you use?
1) RMAN>crosscheck backup;This command will give you the output for the Active and Expired Backups.
2) RMAN>report obsolete;This command will shows you the obsolete backups.
How Recovery Manager Duplicates a Database?
To prepare for database duplication:
For the duplication to work, you must connect RMAN to both the target (primary) database and an auxiliary instance started in NOMOUNT mode.
Allocate at least one auxiliary channel on the auxiliary instance. The principal work of the duplication is performed by the auxiliary channel, which starts a server session on the duplicate host. This channel then restores the necessary backups of the primary database, uses them to create the duplicate database, and initiates recovery.
All backups and archived redo logs used for creating and recovering the duplicate database, however, must be accessible by the server session on the duplicate host.
As part of the duplicating operation, RMAN automates the following steps:
Creates a control file for the duplicate database.
Restores the target datafiles to the duplicate database and performs incomplete recovery by using all available incremental backups and archived redo logs.
Shuts down and starts the auxiliary instance.
Opens the duplicate database with the RESETLOGS option after incomplete recovery to create the online redo logs.
Generates a new, unique DBID for the duplicate database.
Renaming Database Files/Controlfiles/Redo logs/Temp files in RMAN Duplicate Database
• Renaming Control Files in RMAN DUPLICATE DATABASE
When choosing names for the duplicate control files, make sure you set the parameters in the initialization parameter file of the auxiliary database correctly; otherwise,
you could overwrite the control files of the target database.
• Renaming Online Logs in RMAN DUPLICATE DATABASE
RMAN needs new names for the online redo log files of the duplicate database. Either you can specify the names explicitly in the DUPLICATE command,
or you can let RMAN generate them according to the rules listed below:
Order Method Result
1 Specify the LOGFILE clause of DUPLICATE command. Creates online redo logs as specified.
2 Set LOG_FILE_NAME_CONVERT initialization parameter. Transforms target filenames, for example, from log_* to duplog_*. Note that you can specify multiple conversion pairs.
3 Set one of the Oracle Managed Files initialization parameters DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_DEST_n, or DB_RECOVERY_FILE_DEST. Transforms target filenames based on the parameters set. The rules of precedence among these parameters are the same used by the SQL statement ALTER DATABASE ADD LOGFILE.
4 Do none of the preceding steps. Makes the duplicate filenames the same as the filenames from the target. You must specify the NOFILENAMECHECK option when using this method, and the duplicate database should be in a different host so that the online logs of the duplicate do not conflict with the originals.
• Renaming Datafiles in RMAN DUPLICATE DATABASE
There are several means of specifying new names to be used for the datafiles of your duplicate database. Listed in order of precedence, they are:
• Use the RMAN command SET NEWNAME FOR DATAFILE within a RUN block that encloses both the SET NEWNAME commands and the DUPLICATE command.
• Specify the DB_FILE_NAME_CONVERT parameter on the DUPLICATE command to specify a rule for converting filenames for any datafiles not renamed with SET NEWNAME or CONFIGURE AUXNAME.
Note:
The DB_FILE_NAME_CONVERT clause of the DUPLICATE command cannot be used to control generation of new names for files at the duplicate instance which are Oracle Managed Files (OMF) at the target instance.
• Set the DB_CREATE_FILE_DEST initialization parameter to create Oracle Managed Files datafiles at the specified location.
If you do not use any of the preceding options, then the duplicate database reuses the original datafile locations from the target database.
It is possible for SET NEWNAME, or DB_FILE_NAME_CONVERT to generate a name that is already in use in the target database. In this case, specify NOFILENAMECHECK on the DUPLICATE command to avoid an error message.
Renaming Tempfiles in RMAN DUPLICATE DATABASE
RMAN re-creates datafiles for temporary tablespaces as part of the process of duplicating a database. There are several means of specifying locations for duplicate database tempfiles. Listed in order of precedence, they are:
• Use the SET NEWNAME FOR TEMPFILE command within a RUN block that encloses both the SET NEWNAME commands and the DUPLICATE command.
W
Difference between catalog and nocatalog?
RMAN can be used for backup of oracle database in 2 modes.
1. NoCatalog mode
2. Catalog Mode
Recovery catalog is central and can have information of many databases.Here we need to create and maintain a seperate database for RMAN perpose.
In nocatalog mode the information regarding backup will be stored in target db control file. There is a limit to the amount of information can be stored in a control file.
Difference between using recovery catalog and control file?
When new incarnation happens, the old backup information in control file will be lost. It will be preserved in recovery catalog.
In recovery catalog, we can store scripts.
Recovery catalog is central and can have information of many databases.
Can we use same target database as catalog?
No. The recovery catalog should not reside in the target database (database to be backed up), because the database can't be recovered in the mounted state.
How do u know how much RMAN task has been completed?
By querying v$rman_status or v$session_longops
From where list & report commands will get input?
Both the commands command quering v$ and recovery catalog views. V$BACKUP_FILES or many of the recovery catalog views such asRC_DATAFILE_COPY or RC_ARCHIVED_LOG.
Command to delete archive logs older than 7days?
RMAN> delete archivelog all completed before sysdate-7;
What are the differences between crosscheck and validate commands?
Validate command is to examine a backup set and report whether it can be restored successfully where as crosscheck command is to verify the status of backup and
copies recorded in the RMAN repository against the media such as disk or tape.
You are working as a DBA and usually taking HOTBACKUP every night. But one day around 3.00 PM one table is dropped and that table is very useful then how will you recover that table?
If your database is running on oracle 10g version and you already enable the recyclebin configuration then you can easily recover dropped table from user_recyclebin or dba_recyclebin by using flashback feature of oracle 10g.
SQL> select object_name,original_name from user_recyclebin;
BIN$T0xRBK9YSomiRRmhwn/xPA==$0 PAY_PAYMENT_MASTER
SQL> flashback table table2 to before drop;
Flashback complete.
In that case when no recyclebin is enabled with your database then you need to restore your backup on TEST database and enable time based recovery for applying all archives before drop command execution. For an instance, apply archives up to 2:55 PM here.
It is not recommended to perform such recovery on production database directly because it is a huge database will take time.
Backup:
Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.
A level 1 incremental backup can be either of the following types:
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0
Which is one is good, differential (incremental) backup or cumulative (incremental) backup?
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0
What level 0 and full backup:
The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.
Full and level zero backups copy all blocks that have data (and more if you're doing image copies), so they are both "full" in that sense.
But if you want to do cumulative or differential backups, the starting point for those must be a level zero incremental backup.
What is Level 0, Level 1 backup?
A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. A level 1 incremental backup can be either of the following types:
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0
Can we perform level 1 backup without level 0 backup?
If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility < 10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup. If compatibility is >= 10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN.
Will RMAN put the database/tablespace/datafile in backup mode?
Nope.
What is snapshot control file?
What is the difference between backup set and backup piece?
Backup set is logical,Backup sets, which are only created and accessed through RMAN, are the only form in which RMAN can write backups to media managers such as tape drives and tape libraries.
A backup set contains one or more binary files in an RMAN-specific format.
and backup piece is physical,
RMAN command to backup for creating standby database?
RMAN> duplicate target database to standby database ....
How to do cloning by using RMAN?
RMAN> duplicate target database …
Suppose you lost one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?
create the datafile and recover that datafile.
SQL> alter database create datafile ‘…path..’ size n;
RMAN> recover datafile file_id;
What is the difference between hot backup & RMAN backup?
For hot backup, we have to put database in begin backup mode, then take backup.
RMAN won’t put database in backup mode.
How to put manual/user-managed backup in RMAN (recovery catalog)?
By using catalog command.
RMAN> CATALOG START WITH '/tmp/backup.ctl';
What are new features in Oracle 11g RMAN?
What is the difference between auxiliary channel and maintenance channel?
An auxiliary channel is a link to auxiliary instance. If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.
When a Duplicate Database created or tablespace point in time recovery is performed Auxiliary database is used. this database can either on the same host or a different host.
What are new features in Oracle 12c RMAN?
What is RAID? What is RAID0? What is RAID1? What is RAID 10?
RAID: It is a redundant array of independent disk
RAID0: Concatenation and stripping
RAID1: Mirroring
Backup Types:
Online/Hot/Inconsistent backup
Offline/Cold/Consistent Backup
Physical backup
Logical backup
Whole database backup
Incremental backup
Differential backup
Partial backup
A database is running in NOARCHIVELOG mode then which type of backups you can take?
Offline/Cold/Consistent Backup
Can you take partial backups if the Database is running in NOARCHIVELOG mode?
No, Partial backup cannot take while database is not in archive log mode.
Can you take Online Backups if the database is running in NOARCHIVELOG mode?
No, we can’t take online backup while database is running in no archive log mode.A datafile that is backed up online will not be synchronized with any particular SCN, nor it will be synchronized with other data fileor the control files.
How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?
alter database noarchivelog;
archive log list;
You cannot shutdown the database for even some minutes, then in which mode you should run the database?
Database must be running in Archive log mode.
Can you take online backup of a Control file if yes, how?
alter database backup controlfile to trace;
alter database backup controlfile to ‘/oracle/app/ctrl.bak’;
Rman> backup current controlfile;
Rman> backup current controlfile to ‘/backup/ctrlfile.copy’;
Why do you take tablespaces in Backup mode?
The goal of ALTER TABLESPACE BEGIN BACKUP and END BACKUP is to set special actions in the current database files in order to make their copy usable , without affecting the current operations.
Nothing needs to be changed in the current datafile , but, as the copying is done by the external tool( Operating system utility), the only way to have something set in the copy is to do it in the current datafiles before the copy , and revert it back at the end.
Sql> alter tablespace begin backup;
While putting the tablespace in backup mode,
-the hot backup falg in the datafile header is set , so that the copy is identified to be a hot backup copy. This is to manage the backup consistency issue when the copy will be used for recovery.
- Checkpoint is done for the tablespace, so that in case of recovery , no redo generated before that point will be applied. Begin backup command completes only when checkpoint is done.
How do you see information about backups in RMAN?
To view summary of database backups
RMAN> list backup summary;
To list details information about backupsets of database you can give the following command
RMAN> list backupset of database;
To list information about backupset of particular tablespace,
RMAN> list backupset of tablespace users;
To list information about particular datafile
RMAN> list backup of datafile 4;
RMAN Report command can be used to get a list of those datafiles which have not been backed up since last <n> days or to know which backupsets have become obsolete and can be deleted as per configured retention policy
report obsolete
Can you take Image Backups using RMAN?
By default RMAN takes the backup as BACKUPSETS i.e. it combines of multiple datafiles into one or more backupsets.
If you want to take the backup of datafiles as it is then you can take IMAGE backups. To take image backups you have to mention 'AS COPY' option in backup command.
Let us take full database image backup using RMAN.
Now to take Full database IMAGE backup give the following command.
rman> backup as copy database tag 'MyFullImageBackup';
RMAN> backup as copy tablespace users;
RMAN> backup as copy datafile 2,6;
You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
Using CATALOG to add their information to RMAN repository.
RMAN> catalog start with ‘d:\bak’;
You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
RMAN>configure retention policy to redundancy 3;