Frequently Asked Questins on Opatch Utility

Q. How to check opatch version?

$ORACLE_HOME/opatch/opatch version

Q. How to Apply single patch using opatch ?

What are the Pre-checks for applying a patch ?

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.

2.Make sure you have a good backup of database.

3. Make a note of all Invalid objects in the database prior to the patch.

4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

5. You MUST Backup your oracle Home and Inventory

$ tar cvf – $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

Q. What are Opatch Options you have used till now ?

command := apply

lsinventory

prereq

query

rollback

util

version

Q. How to Roll-back a patch ?

In case if a patch did not fix the issue or if you has applied wrong patch to oracle home, you can always rollback the patch using following command.

$  opatch rollback -id <Patch Number>

You can also apply specific patches using ‘NApply’

$  opatch util napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate

Above command will apply patches 1, 2, and 3 which are under <the patch_location> directory.

OPatch will skip duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the ORACLE_HOME)

You can see all the options for NApply using following ‘help’ command.

$ORACLE_HOME/OPatch/opatch util NApply -help

How to find the applied patches?

Using the lsinventory command

$  oracle_home/opatch/opatch lsinventory

For more updates,  Like Us Our Facebook Page Here 

How to Import data via a network link in Oracle

How to Recover archive gaps in standby database - using 2 methods

Using Both Methods

1.       Manually Log Shipping (when the missing logs are very less)

2.       Performing Incremental Backup (When there is a very huge gap)

METHOD 1:

When the logs missing or corrupt is less in number (say below 15), we can ship the logs which were missing in the standby site from the primary site (scp/sftp/ftp) and then we can register the log file in the standby so that the gap can be resolved.

This is easy process if you have missing or corrupt logs in lesser number.

Otherwise we can use the incremental backup strategy, and perform the recovery at standby site.

Lets go through the Archive log Shipping process

First, Find the archives which are missing by issuing the following command. This would give the gap sequences

SQL> select * from v$archive_gap

Or you can use the v$managed_standby view to find where the log apply stuck.

SQL> select sequence#,process,status from v$managed_standby;

Now, Copy the logs to the standby site from the primary site

Using the below command

$ scp log_file_name_n.arc oracle@standby:/log/file/location/log_file_name_n.arc

At standby site, Do the log file registration at the standby site until all the missing log files are registered, Use this below command.

SQL> alter database register logfile '/log/file/location/log_file_name_n.arc';

Now apply would take place and your standby will become sync with the primary.

METHOD 2 :

when the difference is huge (say around 500 logs) the above method is very time consuming and not a proper approach. Else you have to rebuild the standby database from scratch.

As an enhancement from 10g, an incremental backup created with BACKUP INCREMENTAL... FROM SCN can be used to refresh the standby database with changes at the primary database since the last SCN at Standby and then managed recovery can resume.

Step 1:

Use this below command to find the SCN difference, on both the database – Primary DB & Standby DB

SQL> select current_scn from v$database;

Step 2 :

Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Step 3: 

Now Shutdown the standby database

SQL> shut immediate

Step 4:  

On the primary, take an incremental backup from the SCN number where the standby has been stuck:

RMAN> run {

allocate channel c1 type disk format '/u01/backup/%U.bkp';

backup incremental from scn ********* database;

}

Step 5:  On the primary, create a new standby controlfile and copy this file to standby side:

SQL> alter database create standby controlfile as '/u01/backup/for_standby.ctl';

$ scp * oracle@dataguard : /u01/backup

Step 6 :

Bring up the Standby instance in nomount mode:

SQL> startup nomount

Step 7

Now, replace the previous controlfile with this new one created at primary , and Bring the database to MOUNT state.

(Because, this Primary Side control file has the information of the SCN and we have to perform the recovery using this controlfile)

SQL> alter database mount standby database;

Step 8 :

Open the RMAN prompt and Catalog the backup piece.

(Because , RMAN does not know about these files yet; so you must let it know – by a process called cataloging)

$ rman target=/

RMAN> catalog start with '/u01/backup';

Step 9 :

Recover these files:

RMAN> recover database;

Step 10 :

After performing the recovery , exit RMAN and start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Step 11 :

Again Check the SCN’s in primary and standby to make sure that both are in SYNc:

SQL> select current_scn from v$database;

 See Also

Configure Standby in case you re-create redologs of Primary site

Frequently Asked Interview Questions for 2+ Candidate

::     Frequently Asked Interview Questions for 2+ Candidate   ::

Experience Level  :  2 -3 years

What is main purpose of CHECKPOINT?

A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.

A checkpoint has two purposes:

1. to establish data consistency

2. Enable faster database recovery

The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.

LOG_CHECKPOINT_TIMEOUT = 3600;                                // Every one hour

LOG_CHECKPOINT_INTERVAL = 1000;               //number of OS blocks.

What does RESETLOGS option do?

1. Creates a new incarnation of the database, putting a new SCN in all data file headers.

2. Reset Log Sequence number to 1

3. Reformats ONLINE REDO LOGFILES if they exists

In what scenarios open resetlogs required ?

An ALTER DATABASE OPEN RESETLOGS statement is required,

1. after incomplete recovery (Point in Time Recovery) or

2. recovery with a backup control file.

3. recovery with a control file recreated with the reset logs option.

What to do if my Global Inventory is corrupted ?

No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option

-attachHome

./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc

ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name”

CLUSTER_NODES=”{}”

Oracle version 11.2.0.4.0 what does each number refers to?

Oracle version number refers :

11 – for Major database release number

2 – for Database Maintenance release number

0 – for Application server release number

4 – for Component Specific release number

0 – for Platform specific release number

What is Row chaining and how can you find it? and how you will resolve the issue ?

When you are inserting record if a row is not fit into the single block… it will chain across multiple blocks leaving  a pointer between these  blocks.

Find using below query

SQL> analyze table <owner>.<table_name>;

SQL> select table_name,chain_cnt from dba_tables where table_name=’tablename';

To Overcome :

create a table with bigger the block size

1) Create tablespace tbs1 data file ‘/u01/oradata/test/data01.dbf’ size 100m blocksize 16k;

2) alter table tablename move to tbs1;

Here tbs1 is the tablespace name with larger block size and  before creating tablespace it is assumed that you have created a db buffer cache for it.

What is row migration? When does it occur? Where can you find this information?

Row migration happens when update occurs at one column and the row is not adequate to fit in the block then the entire row will be moved to the new block.

Find using below query

SQL> select table_name,chain_cnt from dba_tables where table_name=’tablename';

To Overcome :

-set pct_free storage parameter for table to adequate value

Do you know about statistics , what is the use of it? What kind of statistics exists in database?

Statistics is a collection information about data or database

There are different types of statistics that oracle maintains-

1)System-Statistics: statistics about the hardware like cpu speed,I/O speed,read time write time etc : select * from aux_stats$

2)Object statistics : For a table oracle collects the information about no.of rows,no.of blocks,avg row length etc.We can view

SQL>select table_name,num_rows,blocks,avg_row_len from dba_tables

for index oracle collect statistics on index column about no.of rows,no.of root blocks,no.of branch blocks,no.of leaf blocks,no.of distinct values etc.

See also

Interview Questions for 2+ Year experienced Candidates

Interview Questions on Architecture for 1+ Experienced

DBA 1+ year Interview 

General Tips to Prepare for an Oracle DBA Job Interview *Popular*

 

FREE Sign-Up with Gmail and Receive Pdf's and Updates via Mail -Click Here