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.