Exercise : RMAN

                RMAN Interview Questions

1. What is RMAN ?
Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

Which Files must be backed up?
Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)

 2.   When you take a hot backup putting Tablespace in begin backup mode, Oracle records SCN # from header of a database file.  What happens when you issue hot backup database in RMAN at block level backup? How does RMAN mark the record that the block has been backed up ?  How does RMAN know what blocks were backed up so that it doesn't have to scan them again?

In 11g, there is Oracle Block Change Tracking feature.  Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks.
Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file. RMAN uses SCNs on the block level and the archived redo logs to resolve any inconsistencies in the datafiles from a hot backup. What RMAN does not require is to put the tablespace in BACKUP mode, thus freezing the SCN in the header. Rather, RMAN keeps this information in either your control files or in the RMAN repository (i.e., Recovery Catalog). 

3.  What are the Architectural components of RMAN?

1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces

4.  What are Channels?

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. It is through the allocation of channels that you govern I/O characteristics such as:

Type of I/O device being read or written to, either a disk or an sbt_tape

Number of processes simultaneously accessing an I/O device

Maximum size of files created on I/O devices

Maximum rate at which database files are read

Maximum number of files open at a time

5.  Why is the catalog optional?

Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database.

6.  What does complete RMAN backup consist of ?
A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

7.  What is a Backup set?
A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

8.  What is a Backup piece?
A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:

·       A datafile cannot span backup sets

·       A datafile can span backup pieces as long as it stays within one backup set

·       Datafiles and control files can coexist in the same backup sets

·       Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There's no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece. 

9.  What are the benefits of using RMAN?

1.      Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.

Oracle DBA real time Sceanrio

 During the database running archivelog mount point 100% goes to fill up.What you will do? 

- When 100% archivelog fill up then set new destination of archive log of the database.

- Then run the command switch log file. (Alter system switch log file.)

- Then take the database backup using RMAN delete input.

- Once completed backup then set the once again old archive log destination (log_archive_dest).

- Then once again switch the logfile.

- Finally delete the new destination path.

Creating ASM Instance and diskgroups manually without DBCA

create the password file :

[oracle@oracleasm1 dbs]$ orapwd file=orapw+ASM1 password=oracle entries=5

create required directories :

[oracle@oracleasm1 dbs]$ cd $ORACLE_BASE

[oracle@oracleasm1 oracle]$ cd admin/

[oracle@oracleasm1 admin]$ mkdir +ASM1

[oracle@oracleasm1 admin]$ cd +ASM1/

[oracle@oracleasm1 +ASM1]$ mkdir bdump

[oracle@oracleasm1 +ASM1]$ mkdir udump

[oracle@oracleasm1 +ASM1]$ mkdir dump

[oracle@oracleasm1 +ASM1]$ mkdir cdump

[oracle@oracleasm1 +ASM1]$ mkdir pfile

Create the init+ASM1.ora file:

Using vi editor or any other editor you like, create the init+ASM1.ora file under the $ORACLE_HOME/dbs directory and add the below lines into this file.

[oracle@oracleasm1 dbs]$ vi init+ASM1.ora

#background_dump_dest='/u01/app/oracle/admin/+ASM1/bdump'  //commented

core_dump_dest='/u01/app/oracle/admin/+ASM1/cdump'

instance_type='asm'

large_pool_size=12M

remote_login_passwordfile='SHARED'

#user_dump_dest='/u01/app/oracle/admin/+ASM1/udump'                  //commented

start the instance using that file :

[oracle@oracleasm1 dbs]$ . oraenv

ORACLE_SID = [orcl] ? +ASM1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/grid

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle

[oracle@oracleasm1 dbs]$ rlwrap sqlplus / as sysasm

SQL> startup pfile='/u01/app/oracle/product/11.2.0/grid/dbs/init+ASM1.ora' ;

ASM instance started

Total System Global Area  284565504 bytes

Fixed Size                    1336036 bytes

Variable Size                        258063644 bytes

ASM Cache                 25165824 bytes

ORA-15110: no diskgroups mounted

SQL> alter system set asm_diskstring='/dev/oracleasm/disks';

System altered.

SQL> select name,path,state,mount_status,header_status,group_number from v$asm_disk;

SQL> alter system set asm_diskgroups='NEWDATA','DATA';

System altered.

SQL> create diskgroup data_disk_grp

  2  disk '/dev/oracleasm/disks/NEWDATA' , '/dev/oracleasm/disks/DATAVOL1' ;

create diskgroup data_disk_grp

*

ERROR at line 1:

ORA-15018: diskgroup cannot be created

ORA-15033: disk '/dev/oracleasm/disks/DATAVOL1' belongs to diskgroup "DATA"

SQL> create diskgroup data_disk_grp

  2  disk '/dev/oracleasm/disks/NEWDATA' , '/dev/oracleasm/disks/DATAVOL1'  force;

Diskgroup created.

SQL> set linesize 100

SQL> col path format a30

SQL> select name, path from v$asm_disk where name is not null;

NAME                                          PATH

------------------------------      --------------------------------------------

DATA_DISK_GRP_0000        /dev/oracleasm/disks/NEWDATA

DATA_DISK_GRP_0001        /dev/oracleasm/disks/DATAVOL1

SQL>

SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;

NAME                                          TYPE      TOTAL_MB    FREE_MB

------------------------------ ------ ---------- ----------

DATA                                                                   0    0

DATA_DISK_GRP                     NORMAL             12283      12181

Open the /etc/oratab file and add the following line at the end:

[oracle@oracleasm1 ~]$ vi /etc/oratab

+ASM1:/u01/app/oracle/product/11.2.0/grid:N

Oracle DBA Daily/Weekly/Monthly or Quarterly Checklist/Tasks

Hi, Today I found an Amazing POST on OTN
about the Daily Activities you find on Internet  &  Actual Working in Company as Genuine DBA

check the link below :

https://community.oracle.com/thread/3788846?tstart=0

BASIC RMAN commands

Oracle Recovery Manager (RMAN) satisfies the most manageable backup and recovery, for all Oracle data formats.

RMAN  provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems.

Below are some of the commonly used RMAN commands which you can run through RMAN command line.

All the commands were tested on Oracle database version 11gR2 (11.2.0.4)

**SHOW COMMAND**

1) Shows all parameters.

RMAN> show all;

2) Shows the archivelog deletion policy.

RMAN> show archivelog deletion policy;

3) Shows the number of archivelog backup copies

RMAN> show archivelog backup copies;

4) Shows the auxiliary database information.

RMAN> show auxname;

5) Shows whether optimization is on or off.

RMAN> show backup optimization;

 6) Shows how the normal channel and auxiliary channel are configured.

RMAN> show [auxiliary] channel;

 7) Shows the characteristics of the channel

RMAN> show channel for device type [disk | <media device>;

8) Shows whether control file autobackup is on or off.

RMAN> show controlfile autobackup;

9) Shows the format of the autobackup control file

RMAN> show controlfile autobackup format;

10) Shows the number of datafile backup copies being kept.

RMAN> show datafile backup copies;

11) Shows the default type (disk or tape)

RMAN> show default device type;

12) Shows policy for datafile and control file backups and copies that RMAN marks as obsolete.

RMAN> show retention policy;

13) Shows the encryption algorithm currently in use.

RMAN> show encryption algorithm;

14) Shows the encryption for the database and every tablespace.

RMAN> show encryption for [database | tablespace];

15) Shows the tablespaces excluded from the backup.

RMAN> show exclude;

16) Shows the maximum size for backup sets. The default is unlimited.

RMAN> show maxsetsize;

17) Shows the policy for datafile and control file backups and copies that RMAN marks as obsolete.

RMAN> show retention policy;

18) Shows the snapshot control filename.

RMAN> show snapshot controlfile name;

19) Shows the compression algorithm in force. The default is the ZLIB algorithm.

RMAN> show compression algorithm;

**BACKUP COMMAND**

1) To perform a manual backup of the current control file

RMAN> backup current controlfile;

2) To back up the control file as part of a tablespace backup operation

RMAN> backup tablespace users include current controlfile;

3) To back up the server parameter file

RMAN> backup spfile;

4) To restart an RMAN backup that failed midway through a nightly backup.

RMAN> backup not backed up since time ‘sysdate-1′ database plus archivelog;

5) To force RMAN to back up a file regardless of whether it’s identical to a previously backed up file by specifying the force option

RMAN> backup database force;

By using the force option, you make RMAN back up all the specified files, even if the backup optimization feature is turned on.

6) To backup complete database

RMAN> backup database;

7) To backup database plus archivelogs

RMAN> backup database plus archivelogs;

8) To backup all archive logs

RMAN> backup archivelog all;

9) To backup specific data file

RMAN> backup datafile 5 tag dbfile_5_bkp;

A tag was also added to easily locate this datafile’s backup.

**CATALOG COMMAND**

1) Add user-managed copies of datafile to RMAN repository

RMAN> catalog datafilecopy ‘/u01/oracle/users.bkp';

RMAN> catalog datafilecopy ‘/u01/oracle/users.bkp’ level 0;  (To catalog as incremental level 0 backup)

2) Add uncataloged backup piece to RMAN repository

RMAN> catalog backuppiece ‘ertt2lu4_1_1′;

3) To catalog multiple files ( say you copied production backup to target database for database cloning)

RMAN> catalog start with ‘/backups/source_bkp’ noprompt;

The start with clause specifies that RMAN catalog all valid backup sets, datafile copies, and archived redo logs starting with the string pattern you pass.

4) To catalog all files in the flash recovery area

RMAN> catalog recovery area;

 **REPORT COMMAND**

1) To find out which backups you need to make in order to conform to the retention policy you put in place

RMAN> report need backup;

The output of the report need backup command tells you that you must back up which all database files to comply with your retention policy.

2) To get a report about all the datafiles in a database

RMAN> report schema;

RMAN> report schema at time ‘sysdate-1′;  (from a past point in time)

3) To reports on any obsolete backups

RMAN> crosscheck backup;

RMAN> report obsolete;

Always run the crosscheck command first in order to update the status of the backups in the RMAN repository to that on disk and tape.

**LIST COMMAND**

1) To review RMAN backups of datafiles, archived redo logs, and control files.

RMAN> list backup;

2) List the backups by just the backup files

RMAN> list backup by file;

3) Lists only backup sets and proxy copies but not image copies

RMAN> list backupset;

4) Lists only datafile, archived redo log, and control file copies

RMAN> list copy;

5) Lists backups by tag:

RMAN> list backupset tag ‘full_database_backup';

6) To list the backups of all datafiles and archivelogs of the target database:

RMAN> list backup of database;

7) Lists all incarnations of a database

RMAN> list incarnation;

When you perform an open resetlogs operation, it results in the creation of a new incarnation of the database. When performing recovery operations on such a database, you might want to check the database incarnation

8) Lists all restore points in the target database

RMAN> list restore point;

9) Lists the names of all recovery catalog scripts

RMAN> list script names;

10) Which of the backups of the target database have an expired status in the repository.

RMAN> list expired backup;

11) Which of the archived redo log backups have the expired status

RMAN> list expired archivelog all;

12) To restrict the list of backups  and copies whose status is listed as available

RMAN> list recoverable backup;

13) To view all the restore points in the database

RMAN> list restore point all;

 
 **CROSSCHECK COMMAND** 

1) Cross-checking just backup sets

RMAN> crosscheck backupset;

2) Cross-checking a copy of a database

RMAN> crosscheck copy of database;

3) Cross-checking specific backupsets

RMAN> crosscheck backupset 10, 12;

4) Cross-checking using a backup tag

RMAN> crosscheck backuppiece tag = ‘monthly_backup';

5) Cross-checking a control file copy;

RMAN> crosscheck controlfilecopy ‘/backups/control01.ctl';

6) Cross-checking backups completed after a specific time

RMAN> crosscheck backup of datafile “/u01/oracle//system01.dbf” completed after ‘sysdate-7′;

7)  Cross-checking of all archivelogs and the spfile

RMAN> crosscheck backup of archivelog all spfile;

8) Cross-checking all backups on disk and tape

RMAN> crosscheck backup;

The crosscheck command checks whether the backups still exist. The command checks backup sets, proxy copies, and image copies.

 
 **DELETE COMMAND**

1) To remove both archived redo logs and RMAN backups

RMAN> delete backup;

RMAN always prompts you for confirmation before going ahead and deleting the backup files. You can issue the delete noprompt command to suppress the confirmation prompt. This will also remove the physical file from the backup media

To make sure the repository and the physical media are synchronized, run “RMAN> crosscheck backup;” before running above command

2) To remove all image copies

RMAN> delete copy;

To make sure the repository and the physical media are synchronized, run “RMAN> crosscheck copy;” before running above command

3) To delete specfic backuppiece

RMAN> delete backuppiece 9;

4) To delete copy of controlfile under /backups

RMAN> delete copy of controlfile like ‘/backups/%';

5) To delete backups with specific tag

RMAN> delete backup tag=’double_bkp_prod';

6) To delete bakups of specific tablespace

RMAN> delete backup of tablespace sysaux device type sbt;

******************

You can also use force, expired, obsolete keyword with delete commad:

delete force ..: Deletes the specified files whether they actually exist on media or not and removes their records from the RMAN repository as well

delete expired ..: Deletes only those files marked as expired  as per crosscheck command.

delete obsolete ..: Deletes datafile backups and copies and the archived redo logs and log backups that are recorded as obsolete in the RMAN repository

The delete obsolete command relies only on the backup retention policy in force.

******************

7) To delete all archived redo logs

RMAN> delete archivelog all;

8) To delete already backed up archived redo logs

RMAN> delete archivelog all backed up 2 times to sbt;

9) To delete specific archived redo logs

RMAN> delete archivelog until sequence = 1234;

10) Delete archive logs after taking backup

RMAN> backup device type sbt archivelog all delete all input;

11) Delete stored script

RMAN> delete script full_disk_db;

If you have two scripts—one local and one global—in the same name, then the delete script command drops the local one, not the global one. If you want to drop the global script, you must use the keyword global in the command, as shown here:

RMAN> delete global script full_disk_db;

 **CHANGE COMMAND**

1) Change the status of a backup set to unavailable

RMAN> change backupset 6 unavailable;

You usually do it when you don’t want to delete the backup/copy but you also don’t want to delete that backup/copy (probably it is not available physically on disk)

Once you mark a backup file unavailable, RMAN won’t use that file in a restore or recover operation.

2)  Change the status of a backup set to available again

RMAN> change backupset 6 available;

For example, say you performed a backup using an NFS-mounted disk and that disk subsequently becomes inaccessible for some reasons, just  issue the change command to set the status of the backup as unavailable. Later, once the disk becomes accessible again, you can change its status back to available.

3) To modify a regular consistent database backup into an archival backup:

RMAN> change backup tag ‘initial_db_bkup’ keep forever;

When you make an archival backup with the keep … forever option, RMAN disregards the backup retention time for these backups.

4) To change the archival backup to a normal database backup

RMAN> change backup tag ‘inital_db_backup’ nokeep;

When you run the change … nokeep command, the backup set with the tag inital_db_backup, which was previously designated as a long term archival backup, will once again come under the purview of your configured retention policy.

5) To modify the time period for which you want to retain the archival backups

RMAN> change backupset 12 keep until time ‘sysdate+60′;

After the 60 days are up, the backup will become obsolete and is eligible for deletion by the delete obsolete command.

 **VALIDATE COMMAND**

1) To check all the datafiles and the archived redo logs for physical corruption  without actually performing the backup

RMAN> backup validate database archivelog all;

2) To check for logical corruption  without actually performing the backup

RMAN> backup validate check logical database archivelog all;

The check logical clause means that RMAN will check for logical corruption only.

3) To validate a single backup set

RMAN> validate backupset 5;

4) To validate all datafiles at once

RMAN> validate database;

Note that the validate command can check at a much more granular level than the backup … validate command. You can use the validate command with individual datafiles, backup sets, and even data blocks.

The validate command always skips all the data blocks that were never used, in each of the datafile it validates.

5)  To validate recovery area

RMAN> validate recovery area;

6) To validate all the recovery related files

 RMAN> validate recovery files;

7) To validate the spfile

RMAN> validate spfile;

8) To validate specific tablespace

 RMAN> validate tablespace <tablespace_name>;

9) To validate specific control file copy

 RMAN> validate controlfilecopy <filename>;

10) To validate specific backupset

RMAN> validate backupset <primary_key>;

See Also : OS Interview for DBA

How to work on Gap Resolution in Dataguard

If we have a gap in Dataguard... it doesnt mean that without thinking anything, we will start to rebuild the standby again..

First of all, we have to see why the gap came, there is 2 type of issue occur, 

firstly the archive are not shipping to standby, or after shipping, it is not applying to standby, so first you need to check are they shipped or not, if they are not shipping , check the error in the alertlog of primary or v$archive_dest_
status, through which you can all the info easily, if there is a eror of pasword file, thus archives are not shipping, you just SCP that  "password file" to standby and rename it..through the above view you will get all the info, why the gap occured, what is the actual reason behind it, first correct that error, then according to need take the incremental or full and build the standby

Recovery Related Doubts I faced During my Initial Stage

Q. Why do i have to take backup of redologfiles in hot backup ?

Q. Can i take backups without the redolog files and is it a valid backup ?

Q. If my database is noarchivelog mode and i put all the tablespace in hot backup can i still take hot backup ? and do restore ?

Answer :

You can take hot backup without redo log file and it is valid backup.

And if your database is in no archivelog mode then first of all you can’t take hot backup.