Oracle DBA Interview Questions and Answers

Saturday, August 8, 2015

RMAN Q & A Page - 10

RMAN INTERVIEW QUESTIONS AND ANSWERS

Page – 10

91. How do you configure RMAN backup for 100 GB database? How we would estimate backup size backup time?

Check your actual size of your database for RMAN backup size almost depends on your actual size of database.

SELECT SUM(BYTES)/1024/1024/104 FROM DBA_SEGMENTS;

Backup time depends on you hardware configuration of your server such as CPU, Memory, and Storage.

Later you can also minimize the backup time by configuring multiple channels with the backup scripts

92. Why use RMAN tool to backup and recover oracle Database?

Organization may have some wonderful custom build unix script which may be backing up the databases without any issued, however from administrative point of view RMAN is supported by oracle and if have any issue with backup and recovery using RMAN you can always rely on oracle debug.

RMAN can be used in two modes. RMAN can be operated as command-line and Enterprise Manager based tool oracle RMAN tool has few interesting features where you could backup only changed blocks, that avoid backing the entire database. This method is called incremental backups. When size of the database is enormous incremental backup significantly reduces the time taken to backup the database.

93 What are the difference between crosscheck and validate commands?

Validate command is to examine the backup set and report whether it can be resorted. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only process files created on the same device type as the channel running crosscheck.

94. What is the difference between backup set and backup piece?

A). Backup set is logical and backup piece is phycial

B). RMAN command to backup for creating standby database

C). Duplicate target database

You can loss 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 datafile(new).

95. How do you restore / recover the backup?

Create datafile and recover datafiles

SQL> alter database create datafile ‘/u01/app/oracle/oradata/syz.dbf’ size 2 G;

RMAN > recover datafile file_id;

96 command to delete archivelogs older than 7 days?

RMAN> delete archivelog all completed before sysdate-7;

97. How to view the current database ?

RAMN> Show all;

98. What Is the incremental checkpoint?

In incremental checkpoint process, CKPT process records lowest low REA to the control file to keep advancing the buffer checkpoint queue(BCQ) to make easy and faster active checkpoint queue(ACQ).

99. Does incremental checkpoint recorded in the alertlog? How to disable it?

Incremental checkpoints in alert_log we can enable / disable recording using parameter log_checkpoints_to_alert=true/false

100. What is the significance of incarnation and DBID in the RMAN backup?

Incarnation help to go backup to old position of the database

DBID means database id. Incarnation is effectively a new version of database that happens when you reset the online redolog using alter….

RMAN Q & A Page - 9

RMAN INTERVIEW QUESTIONS AND ANSWERS

Page – 9

81. My database was terminated while in BACKUP MODE can I do the recover?

If the database was terminated while one of its tablespace was in BACKUP MODE(ALTER TABLESPACE xyz BEGIN BACKUP;) it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logos to the database.

ALTER DATABASE DATAFILE ‘path/filename’ END BACKUP;

One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time.

ALTER DATABASE END BACKUP;

This command must be issued when the database is mounted, but not yet opened

82. How can one to clone / duplicate a database with RMAN?

This first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file on the machine. Review the all parameters and make the required to change.

EXAMPLE- DB_NAME parameter to the new database’s name

Secondly you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to the AUXILIARY.

Finally write a RMAN script like this to do the cloning, and call it with “rman cmdfile dupdp.rcv”

Connect target sys / secure@origdb

Connect catalog rman/rman@catdb

Connect auxiliary /

Run{

set newname for datafile 1 to ‘/ORADATA/u01/system01.dbf’;

set newname for datafile 2 to ‘/ORADATA/u02/undotbs01.dbf’;

set newname for datafile 3 to ‘/ORADATA/u03/users01.dbf’;

set newname for datafile 4 to ‘/ORADATA/u03/index01.dbf’;

set newname for datafile 1 to ‘/ORADATA/u01/example01.dbf’;

allocate auxillary channel dudp1 type disk;

set until sequence 2 thread 1;

duplicate target database to dupdp

logfile

GROUP 1 (‘/ORADATA/u02/redo01.log’) SOZE 200K REUSE;

GROUP 2 (‘/ORADATA/u03/redo02.log’) SIZE 200K REUSE;

}

The above script will connect to the “target “ (database that will be cloned), the recovery catalog and the auxiliary database(new duplicate DB) previous will be restored and the database recovered to the “set until time” specification in the script.

NOTES

“set new name” commands are only required if your datafile names will different from the target database.

The new cloned DB will have its own unique DBID

83. What are the various tape backup solution available in the market?

A). EMC’ Legato Networker Module for Oracle

B). Symantec’s Netback up database agent for Oracle

C). IBM’s Tivoli storage management RMAN and the traditional backup methods.

84. How to verify the integrity of the image copy of the RMAN?

RMAN>catalog datafile copy ‘f:testsystem.dbf’;

RMAN>backup validate check logical datafile ‘f:testsystem.dbf’;

SQL> select * from v$database_block_corruption;

85. How to generate the begin backup script?

SQL> set head off

SQL>spool beginbackup.sql

SQL>select ‘alter tablespace ‘ tablespace_name ‘begin backup’ from dba_tablespace;

SQL>spool off

This will create the file beginbackup.sql with entry for all tablespace, remove any unnecessary lines & then execute this script into SQL like

SQL>@beginbackup.spl;

Once execute the above script this will put all the tablespace in to begin backup mode.

Now create backup of your control file in human readable format like..

Alter database backup control file to trace as ‘/some/path’

You can reuse it by removing comment at beginning & replace them with connect / as sysdba

Then copy all your datafiles, redo logs and control file from your database sever backup location. After datafiles are copied don’t’ forget to end backup for all tablespace here is the scripts

SQL>set head off;

SQL>spool endbackup.spqql;

SQL>select ‘alter tablespace ‘tablespace_name’ end backup’ from tablespace;

SQL>spool off;

This will create file endbackup.sql with entry for all tablespaces, remove any unnecessary lines and then execute this script into SQL like..

SQL>@endbackup.sql;

Once you execute this script this will put all tablespace in to end backup

86. what is the auxiliary channel in RMAN? When we need this?

An auxiliary channel is a like to auxiliary instance. If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least 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.

RUN

{

ALLOCATE AUIXILARY CHANNEL ch DEVICE TYPE SBT;

ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL AUX2 DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL AUX3 DEVICE TYPE DISK;

DUPLIACATE TARGET DATABASE TO dupdb;

}

87. How to setup the physical standby database with RMAN?

You can use either manual techniques or the RMAN DUPLICATE command to create a standby database from backup of your primary database. Creating a standby database with RMAN has the following advantages over manual techniques.

A). RMAN can create a standby database by copying the files currently in use by the primary database. No backups are required.

B). RMAN can create a standby database by restoring backup of the primary database to the standby site. Thus the primary database is not affected during the creation of the standby database.

C). RMAN automates renaming of files, including oracle management files (OMF) and directory structures.

D). RMAN restores archived redolog files from backups and performs media recovery so that the standby and primary database are synchronized

88. How to create a tablespace?

SQL> create tablespace RTBS datafile ‘D\ORACLE\ORADATA\RTBS01.DBF’ size 200m

extent management local uniform size 5m;

89. How to create the catalog user?

SQL> create user CATALOG indentified by CATALOG default tablespace RTBS quota unlimited on RTBS;

90. How to connect the catalog and target database?

%rman target sys/oracle@target_db

RMAN> connect catalog RMAN_USER/RMAN_PASSWORD@cat_db;

RMAN Q & A Page - 8

RMAN INTERVIEW QUESTIONS AND ANSWERS

Page – 8

71. How do you delete expired archive log files using RMAN?

RMAN>list expired archivelog all;

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

RMAN>list expired archivelog all;

Output

RMAN>list expired archivelog all;

Specification does not match any archive any archived log in the repository

72. How do create a recovery catalog?

STEP - 1

create separate tablespace to store rman catalog tables

SQL> create tablespace rmancatalog_tbs DATAFILE

‘u01/app/oracle/oradata/rmandv1/rman_catalog.dbf’

SIZE 250M AUTOEXTEND OFF

EXTENT MANAGMEN LOCAL SEGMENT SPACE MANAGEMENT AUTO;

SETP - 2

Create rman user

SQL> CREATE USER rman IDENTIFIED BY rman001

DEFAULT TABLESPACE rmancatalog_tbs

QUOTA UNLIMITED ON rmancatalog_tbs

TEMPORARY TABLESPACE TEMP;

STEP – 3

SQL> GRANT recovery_catalog_owner to rman;

73. Why and when should I take backup the database?

Backup and recovery is one of the most important aspects of a DBA’s job

Normally one would schedule a hierarchy of daily, weekly, monthly backups, however consult with your users before deciding on a backup schedule. Backup frequently normally depends on the following factors

A). Rate of data change / transaction rare

B). Database availability / can you shutdown for cold backups

C) critically of the data / value of the data to the company

D). Read-only tablespace needs backing up just once right after make it read-only

E). If you are running archivelog mode you can backup parts of a database over an extended cycle of days

F). If archive logging is enabled one needs to backup archive log files timorously to prevent database freezes

Carefully plan for backup retention periods. Ensure enough backup media are available and that old backups are expired in-time to make media available for new backups. Off site vaulting is also highly recommended

74. What are all the strategies available for backup an oracle database?

A). Export / Import

B). Cold or off line backups

C) Hot backups

D) RMAN Backups

75. What is the difference between restoring and recovering?

Restoring involves copying the backup files from secondary storage to disk. This can be done to replace damaged files or to copy /move a database to a new location.

Recovery is the process of applying redo logs to the database to roll it forward. One can roll forward until a specific point in time(before the disaster occurred) or roll forward until the last transaction recorded in the log files.

76. How does one backup a database using the export utility?

Oracle exports are “logical” database backups(not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally backup the physical data files.

One of the advantags of exports is that one can selectively re-import tables, however one cannot roll-forward from an restored export. To completely restore a database from export file one practically needs to recreate the entire database.

Always do full system level exports(FULL=YES) full exports include more information about the database in the export file than user level exports.

77. How does one put a database into ARCHIVELOG mode?

The main for running in archivelog mode is that one can provide 24X7 availability and guarantee complete data recoverability. It is necessary to enable ARCHIVELOG mode before one can start to use online database backups.

The following commands to place the database into ARCHIVELOG mode

SQL> CONNECT sys AS SYSDBA

SQL>STARTUP MOUNT EXCULSIVE;

SQL>ALTER DATABASE ARCHIVELOG;

SQL>ALTER DATABASE OPEN;

The following parameters needs to be set for databases in ARCHIVELOG mode

Log_archive_start = TRUE

Log_archive_dest_1 =’LOCATION-/arch_dir_name’

Log_arhive_dest_state_1 = ENANLE

Log_archive_format = %d_%t_%s.arc

NOTE – 1

Remember to take a baseline database backup right after enabling archivelog mode, without it one would not be able to recover. Also implement an archivelog backup to prevent the archive log directory from filling up

NOTE – 2

ARCHIVELOG mode is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backup.

NOTE – 3

You may want to set the INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=’TRUE, log_archive_dest=and log_archive_format=…

NOTE – 4

You can change the archivelog destination of a database on-line with the ARCHIVE LOG START To ’directory’ statement. This statement is often used to switch archiving between a set of directories

NOTE – 5

When running oracle real application clusters. You need to shut down all the nodes before changing the database to ARCHIVELOG mode.

78. I have an archived / online REDO LOG file, can I get my DB back?

The following INIT/SPFILE parameter can be used, if your current redologs are corrupted we can do the database recovery, If one of the archived log file missing over it cannot be restored.

NOTE

Caution is advised when enabling this parameter as you might end up losing your entire database allow_reset;pgs_corruption = true

This should allow you to open the database. However, after using this parameter your db will be inconsistent.

STEPS

A). Do a “SHUT DOWN NORMAL” of the database

B). Do a “STARTUP MOUNT” and “ ALTER DATABASE OPEN RESETLOGS”

C). If the database asks for recovery use an UNTIL CANCEL type recovery and apply all the available archive and on-line redologs then issue CANCEL and re issue the “ALTER DATABASE OPEN LOGS” command

Wait a couple of minutes for oracle to sort itself out,

Do a “ SHUTDOWN NORMAL”

Remove the above parameter

Do a database “STARTUP and check your ALTER LOG file for errors

Extract the data and rebuild the entire DATABASE

79. How does one do off-line database backup?

Shut down the database from sqlplus or server. Backup all the files to secondary storage(Ex.. tapes) Ensure that your backup includes the all data files, all control files and all log files when completed restart your database

Do the following queries to get a list of all the files needs to be backed up

Select name from sys. V_$datafile;

Select member from sys. V_$logfile;

Select name from sys v_$controlfile;

Shutdown the db with the following commands

Alter system checkpoint;

Shutdown abort;

Startup restrict;

Shutdown immediate;

Note that if your database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your DATABASE down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line)backups

80. How does one can do on-line database backups?

Each tabelspace that needs to be backed up must be switched into backup

Mode before copying he files out to secondary storage(Tapes). Look at this simple example

ALTER TABLESPACE xyz BEGIN BACKUP;

! cp xyzFile1 /backupDir/

ALTER TABESPACE XYZ END BACKUP;

It is better to backup tablespace for tablespaces than to put all tablespace in backup mode. Backing them up separately incurs less overhead. When done remember to backup your control files.

EXAMPLE

ALTER SYSTEM SWITCH LOGFILE

Headers

ALTER DATABASE BACKUP CONTROLFILE TO ‘backupDir/control.dbf’;

NOTE

Do not run on-line backup during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while backup mode. This will lead to excessive database archiving and even database freezes.

RMAN Q & A Page - 7

RMAN INTERVIEW QUESTIONS AND ANSWERS

Page – 7

61. What are the various reports available with RMAN?

RMAN>list backup;

RMAN>list archive;

61.How do you see the information about backups in RMAN?

RMAN> list backup:

Use this sql statements to check

SQL> select sid totalwork sofar from v$session_longops where sid 153;

here we gave sid when back start it will show SID

62. List the advantages of RMAN backup comparing with the traditional hot backups?

A). Ability to perform INCREMENTAL BACKUP

B). Ability to recover one block of datafile

C). Ability to automatically backup CONTROLFIEL and SPFILE

D). Ability to Automatically To delete the ARCHIVE REDDOLOG files, with the new one’s automatically .

E). Ability to perform backup and restore with parallelism

F). Ability to RESTART the failed backup, without starting from beginning

G). Much faster comparing with other traditional backup strategie.

63. How to clone the database using RMAN ?

A). Duplicate

B). Restore.

64.How do you setup the RMAN tape backups?

RMAN Target /

Run

{

Allocate channel ch1 device tape sbt_tape maxpiecesize 4g

Format ’D_U_T_t’;

sql ‘alter system switch logfile’;

backup databases;

backup archivelog from time ‘sysdate-7’;

backup Format ‘D_CTLFILE_P_U_T_t’ current controlfile

release channel ch1;

}

The above script for Tivoli backup server.

66. What is the difference between physical backup and logical backups?

In oracle logical backup is “ which is taken using either Traditional export / import or latest data dump. “Where as physical backup is known as when you take the physical O/S database files as backup.”

67. What is the meaning of RAID? What is RAID0,RAID1?

RAID: It is redundant array of independent disk

RAID0: concatenation and strippin

RAID1: Mirroring

67. Difference between hot backup and cold backup?

Hot backup taken when the database is on, cold backup is taken during the shut down period

68. How do you test that your recovery was successful?

SQL> select count(*) from flashback_table;

69. You have taken a manual backup of datafile using O/S, How RMAN can identified the backup?

RMAN>catalogdatafilecopy ‘DB01/BACKUP/userso1.pdf’;

Restriction> Accessible on disk> A complete copy of single file

69. How can you display warning message?

SQL> select object_type. message_message_type,message_level, reason, suggested_action from dab_outstanding_alerts;

70. How do you backup the entire database?

SQL> BACKUP DATABASE;

RMAN Q & A Page – 6

RMAN INTERVIEW QUESTIONS AND ANSWERS

Page – 6

51. How to perform the cancel based recovery from the full database via hot backup?

CANCEL BASED RECOVERY

Cancel base recovery allows the DBA to cancel recovery at a desired point. This situation is most likely occur if archive logfiles or redo logfiles needed for recovery are lost or damaged and cannot be restored. In this situation, you would apply all logs until you reached the missing files and then cancel the recovery

FOLLOW THES STEPS TO EXECUTE A CANCEL BASED RECOVERY

A). If the database is still open, shut down the database using the shutdown command with the ABORT option.

B). 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

C). Correct the problem that caused the media failure. If the problem cannot be corrected, the datafiles must be restored to an alternate location. If this is the case, the ALTER TABLESPACE RENAME DATAFILE command must be used to change the location of the datafile in the control file .

D). If the current control file do not match the physical structure of the database at the time you want to recover to, restore a backup of the control file that matches the database’s physical file structure at the point in time you want to recover to replace all current control file of the database with the one you want to use for recovery. If you do not have a backup copy of the control file you can create new one,

E). Restore backups of all data files. Make sure the backups where taken before the point in time you are going to recover to. Any data files added after the point in time you are recovering 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.

F). Make sure read-only tablespace are offline before you start recovery so recovery does not try to update the datafile headers.

G). Start SQL*PLUS and connect oracle as SYS

H). Start the instance and mount the database using STRARTUP command with the MOUNT option.

I). If you restored files to alternative location, Change the location now in the control file by using the ALTER TABLESPACE RENAME DATAFILE command.

J). Use the RECOVER DATABASE UNTIL CANCEL command to begin cancel-based recovery. If a backup of the control file is being used, make sure to specify the USING BACKUP parameter.

K). You can check the ALERT file to see if you incomplete recovery was actually a complete recovery. If the recovery was a complete recovery the message in the ALERT file is as follows

L). RESETLOGS after complete recovery through change scn. If the recovery was incomplete, the following message is recored.

M). RESETLOGS after incomplete recovery UNTIL CHANGE scn

After opening the database using the RESETLOGS option, perform a normal shutdown and a full database backup. If you do not do this, any changes made after the recovery and before the next full backup are unrecoverable . If you did not reset the logs, the database is still recoverable

52.Where the RMAN store the data without the catalog?

CATALOG vs NOCATALOG the difference in only, who maintains the backup records like, when is the last successful backup, incremental, differential etc.. In CATALOG mode another database( TARGET DATABASE) stores all the information. In NOCATALOG mode, controfile of target database is responsible.

53. How to indentified the database(s) backed-up by RMAN?

You don’t have any view to identified whether it is backed up or not. The only option is connect to the target database and give list backup, it will give us the backup information with data and timing.

RMAN> Show all;

54. How to indentified the expired, active, obsolete, backups? Which RMAN command useful for us?

RMAN>crosscheck backup;

RMAN>crosscheck archivelog all;

RMAN>list backup;

RMAN>list archive logall;

55. How do delete old obsolete and expired oracle RMAN backup?

If you unable to delete obsolete and expired oracle DB backup from the catalog, it will cause some unnecessary throw error message during backup and also it will take-up space at the file system level.

Also it is not recommended to directly remove the RMAN backup files from the file system using linux rm command

The main parameter that decides what to delete is the retention policy. To identify your retention policy connect using RMAN and execute “show all” command

RMAN> show all;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW FO 4 DAYS;

56. What is an obsolete backup?

Obsolete backup means the backup pieces is still available but it is no longer needed, it depends on retention

57.How to view the backups before delete obsolete.

Before we delete, obsolete command it is always recommend that the

RMAN cross check backup command will check the records to make sure the accurate the record in the RMAN cataloag.

RMAN> CROSSCHECK BACKUP;

59. How to view the backup after delete obsolete?

RMAN>LIST BACKUP SUMMARY;

60. What is an expired backup?

Backup piece or backup set not found in the backup destination