DBA interview Question and Answer Part 2

DBA interview Question and Answer Part 2

I have configured the RMAN with Recovery window of 3 days but on my backup destination only one days archive log is visible while 3 days database backup is available there why?

I go through the issue by checking the backup details using the list command. I found there is already 3 days database as well as archivelog backup list is available. Also the backup is in Recoverable backup. Thus it is clear due to any reason the backup is not stored on Backup place.

Connect rman target database with catalog

List backup Summary;

List Archivelog All;

List Backup Recoverable;

When I check the db_recovery_dest_size, it is 5 GB and our flash-recovery area is almost full because of that it will automatically delete archive logs from backup location. When I increase the db_recovery_dest_size then it is working fine.

If one or all of control file is get corrupted and you are unable to start database then how can you perform recovery?

If one of your control file is missing or corrupted then you have two options to recover it either delete corrupted CONTROLFILE manually from the location and copy the available rest of CONTROLFILE and rename it as per the deleted one. You can check the alert.log for exact name and location of the control file. Another option is delete the corrupted CONTROLFILE and remove the location from Pfile/Spfile. After removing said control file from spfile and start your database.

In another scenario if all of your CONTROLFILE is get corrupted then you need to restore them using RMAN.

As currently none of the CONTROLFILE is mounted so RMAN does not know about the backup or any pre-configured RMAN setting. In order to use the backup we need to pass the DBID (SET DBID=691421794‎) to the RMAN.

RMAN>Restore Controlfile from ‘H:\oracle\Backup\ C-1239150297-20130418’

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.

Note: If you are using SYS user to drop any table then user’s object will not go to the recyclebin for SYSTEM tablespace, even you have already set recyclebin parameter ‘true’.

And If you database is running on oracle 9i you require in-complete recovery for the same.

Sometimes why more archivelog is Generating?

There are many reasons such as: if more database changes were performed either using any import/export work or batch jobs or any special task or taking hot backup (For more details why hot backup generating more archive check my separate post).You can check it using enabling log Minor utility.

How can I know my require table is available in export dump file or not?

You can create index file for export dump file using ‘import with index file’ command. A text file will be generating with all table and index object name with number of rows. You can confirm your require table object from this text file.

What is Cache Fusion Technology?

Cache fusion provides a service that allows oracle to keep track of which nodes are writing to which block and ensure that two nodes do not updates duplicates copies of the same block. Cache fusion technology can provides more resource and increase concurrency of users internally. Here multiple caches can able to join and act into one global cache. Thus solving the issues like data consistency internally without any impact on the application code or design.

Why we should we need to open database using RESETLOGS after finishing incomplete recovery?

When we are performing incomplete recovery that means, it is clear we are bringing our database to past time or re-wind period of time. Thus this recovery makes database in prior state of database. The forward sequence of number already available after performing recovery, due to mismatching of this sequence numbers and prior state of database, it needs open database with new sequence number of redo log and archive log.

Why export backup is called as logical backup?

Export dump file doesn’t backup or contain any physical structure of database such as datafiles, redolog files, pfile and password file etc. Instead of physical structure, export dump contains logical structure of database like definition of tablespace, segment, schema etc. Due to these reason export dump is call logical backup.

What are difference between 9i and 10g OEM?

In oracle 9i OEM having limited capability or resource compares to oracle 10g grids. There are too many enhancements in 10g OEM over 9i, several tools such as AWR and ADDM has been incorporated and there is SQL Tuning advisor also available.

Can we use same target database as catalog DB?

The recovery catalog should not reside in the target database because recovery catalog must be protected in the event of loss of the target database.

What is difference between CROSSCHECK and VALIDATE command?

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.

How do you identify or fix block Corruption in RMAN database?

You can use the v$block_corruption view to identify which block is corrupted then use the ‘blockrecover’ command to recover it.

SQL>select file# block# from v$database_block_corruption;

file# block

10 1435

RMAN>blockrecover datafile 10 block 1435;

What is auxiliary channel in RMAN? When it is required?

An auxiliary channel is a link to auxiliary instance. If you do not have automatic channel configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.

Explain the use of Setting GLOBAL_NAME equal to true?

Setting GLOBAL_NAMES indicates how you might connect to the database. This variable is either ‘TRUE’ or ‘FALSE’ and if it is set to ‘TRUE’ which enforces database links to have the same name as the remote database to which they are linking.

How can you say your data in database is Valid or secure?

If data of the database is validated we can say that our database is secured. There is different way to validate the data:

1. Accept only valid data

2. Reject bad data.

3. Sanitize bad data.

Write a query to display all the odd number from table.

Select * from (select employee_number, rownum rn from pay_employee_personal_info)

where MOD (rn, 2) <> 0;

-or- you can perform the same things through the below function.

set serveroutput on;
begin
for v_c1 in (select num from tab_no) loop
if mod(v_c1.num,2) = 1 then
dbms_output.put_line(v_c1.num);
end if;
end loop;
end;

What is difference between Trim and Truncate?

Truncate is a DDL command which delete the contents of a table completely, without affecting the table structures where as Trim is a function which changes the column output in select statement or to remove the blank space from left and right of the string.

When to use the option clause "PASSWORD FILE" in the RMAN DUPLICATE command?
If you create a duplicate DB not a standby DB, then RMAN does not copy the password file by default. You can specify the PASSWORD FILE option to indicate that RMAN should overwrite the existing password file on the auxiliary instance and if you create a standby DB, then RMAN copies the password file by default to the standby host overwriting the existing password file.

What is Oracle Golden Gate?

Oracle GoldenGate is oracle’s strategic solution for real time data integration. Oracle GoldenGate captures, filters, routes, verifies, transforms, and delivers transactional data in real-time, across Oracle and heterogeneous environments with very low impact and preserved transaction integrity. The transaction data management provides read consistency, maintaining referential integrity between source and target systems.

What is meaning of LGWR SYNC and LGWR ASYNC in log archive destination parameter for standby configuration.

When use LGWR with SYNC, it means once network I/O initiated, LGWR has to wait for completion of network I/O before write processing. LGWR with ASYNC means LGWR doesn’t wait to finish network I/O and continuing write processing.

What is the truncate command enhancement in Oracle 12c?

In the previous release, there was not a direct option available to truncate a master table while child table exist and having records.

Now the truncate table with cascade option in 12c truncates the records in master as well as all referenced child table with an enabled ON DELETE constraint.