Top 5 issues for Instance Eviction {Doc ID 1374110.1}

Issue #1  The alert.log shows ora-29740 as a reason for instance crash/eviction

Symptoms:

An instance crashes and the alert.log shows "ORA-29740: evicted by member ..." error.

Possible causes:

An ORA-29740 error occurs when an instance evicts another instance in a RAC database.  The instance that gets evicted reports ora-29740 error in the alert.log.
Some of the reasons for this are a communications error in the cluster, failure to issue a heartbeat to the control file, and other reasons. 

Checking the lmon trace files of all instances is very important to determine the reason code.  Look for the line with "kjxgrrcfgchk: Initiating reconfig".
This will give a reason code such as "kjxgrrcfgchk: Initiating reconfig, reason 3".  Most of the ora-29740 error when an instance is evicted is due to reason 3 which means "Communications Failure".

a) Network Problems.
b) Resource Starvation (CPU, I/O, etc..)
c) Severe Contention in Database.
d) An Oracle bug.

Solutions:

1) Check network and make sure there is no network error such as UDP error or IP packet loss or failure errors.
2) Check network configuration to make sure that all network configurations are set up correctly on all nodes.
   For example, MTU size must be same on all nodes and the switch can support MTU size of 9000 if jumbo frame is used.
3) Check if the server had a CPU load problem or a free memory shortage.
4) Check if the database was hanging or having a severe performance problem prior to the instance eviction.
5) Check CHM (Cluster Health Monitor) output to see if the server had a CPU or memory load problem, network problem, or spinning lmd or lms processes.
6)  Having OSWatcher output is helpful when CHM output is not available.

Issue #2  The alert.log shows "ipc send timeout" error before the instance crashes or is evicted

Symptoms:

An instance is evicted with alert.log showing many "IPC send timeout" errors.  This message normally accompanies a database performance problem.

Possible causes:

In RAC, processes like lmon, lmd, and lms processes constantly talk to processes in other instances.  The lmd0 process  is responsible for managing enqueues while lms processes are responsible for managing data block resources and transferring data blocks to support the cache fusion.  When one or more of these processes are stuck, spin, or are extremely busy with the load, then these processes can cause the "IPC send timeout" error.

Another cause of "IPC send timeout" error reported by lmon, lms, and lmd processes is the  network problem or the server resource (CPU and memory) issue.  Those processes may not get scheduled to run on CPU or the network packet sent by those processes can get lost.

The communication problem involving lmon, lmd, and lms processes causes an instance eviction.  The alert.log of the evicting instance shows messages similar to

IPC Send timeout detected.Sender: ospid 1519
Receiver: inst 8 binc 997466802 ospid 23309

If an instance is evicted, the "IPC Send timeout detected" in alert.log is normally followed by other issues like ora-29740 and "Waiting for clusterware split-brain resolution"

Solutions:

1) Check network and make sure there is no network error such as UDP error or IP packet loss or failure errors.
2) Check network configuration to make sure that all network configurations are set up correctly on all nodes.
   For example, MTU size must be same on all nodes and the switch can support MTU size of 9000 if jumbo frame is used.
3) Check if the server had a CPU load problem or a free memory shortage.
4) Check if the database was hanging or having a severe performance problem prior to the instance eviction.
5) Check CHM (Cluster Health Monitor) output to see if the server had a CPU or memory load problem, network problem, or spinning lmd or lms processes.
6)  Having OSWatcher output is helpful when CHM output is not available.

Issue #3  The problem instance was hanging before the instance crashes or is evicted

Symptoms:

The instance or database was hanging before the instance crashed/evicted.  It could also be that the node hang.

Possible causes:

Different processes such as lmon, lmd, and lms communicate with corresponding processes on other instances, so when the instance and database hang, those processes may be waiting for a resource such as a latch, an enqueue, or a data block.  Those processes that are waiting can not respond to the network ping or send any communication over the network to the remote instances.  As a result, other instances evict the problem instance.

You may see a message similar to the following in the alert.log of the instance that is evicting another instance:
Remote instance kill is issued [112:1]: 8
or
Evicting instance 2 from cluster

Solutions:

1) Find out the reason for the database or instance hang. Getting a global system state dump and global hang analyze output is critical when troubleshooting the database or instance hang issue. If the global system state dump can not be obtained, get the local system state dump from all instances around same time.
2) Check CHM (Cluster Health Monitor) output to see if the server had a CPU or memory load problem, network problem, or spinning lmd or lms processes.

3) Having OSWatcher output is helpful when CHM output is not available.

Issue #4  The alert.log shows "Waiting for clusterware split-brain resolution" before one or more instances crashes or is evicted

Symptoms:

Before one of more instances crash, the alert.log shows "Waiting for clusterware split-brain resolution".  This is often followed by "Evicting instance n from cluster" where n is the instance number that is getting evicted.

Possible causes:

The lmon process sends a network ping to remote instances, and if lmon processes on the remote instances do not respond, a split brain at the instance level occurred.  Therefore, finding out the reason that the lmon can not communicate with each other is important in resolving this issue.

The common causes are:
1) The instance level split brain is frequently caused by the network problem, so checking the network setting and connectivity is important.  However, since the clusterware (CRS) would have failed if the network is down, the network is likely not down as long as both CRS and database use the same network.  
2) The server is very busy and/or the amount of free memory is low -- heavy swapping and scanning or memory will prevent lmon processes from getting scheduled. 
3) The database or instance is hanging and lmon process is stuck.
4) Oracle bug

The above causes are similar to the causes for the issue #1 (The alert.log shows ora-29740 as a reason for instance crash/eviction).

Solutions:

The solution in here is similar to issue #1.

1) Check network and make sure there is no network error such as UDP error or IP packet loss or failure errors.
2) Check network configuration to make sure that all network configurations are set up correctly on all nodes.
   For example, MTU size must be same on all nodes and the switch can support MTU size of 9000 if jumbo frame is used.
3) Check if the server had a CPU load problem or a free memory shortage.
4) Check if the database was hanging or having a severe performance problem prior to the instance eviction.
5) Check CHM (Cluster Health Monitor) output to see if the server had a CPU or memory load problem, network problem, or spinning lmd or  lms processes.

6)  Having OSWatcher output is helpful when CHM output is not available.

Issue #5  The problem instance is killed by CRS because another instance tried to evict the problem instance and could not evict it.

Symptoms:

When an instance evicts another instance, all instance waits until the problem instance shuts down itself, but if the problem instance does not terminate for any reason,
the same instance that initiated the eviction issues a member kill request.  The member kill request asks the CRS to kill the problem instance.  This feature is available from 11.1 and higher.

Possible causes:

The alert.log of the instance that is asking CRS to kill the problem instance shows
Remote instance kill is issued [112:1]: 8

For example, the above message means that the member kill request to kill the instance 8 is sent to CRS.

The problem instance is hanging for any reason and is not responsive.  This could be due to the node having CPU and memory problem, and the processes for the problem instance is not getting scheduled to run on CPU.

The second common cause is a severe contention in the database is preventing the problem instance from realizing that remote instances evicted the instance.

Another cause could be due to the one or more processes surviving the "shutdown abort" when the instance tries to abort itself.  Unless all processes for the instance is killed, CRS does not think the instance terminated and will not inform other instances that the problem instance aborted.  One common problem for this is that one or more processes become defunct processes and do not terminate.
This leads to the recycle of CRS either through a node reboot or a rebootless restart of CRS (node does not get rebooted but CRS gets restarted). 
In this case, the alert.log if the problem instance shows
Instance termination failed to kill one or more processes
Instance terminated by LMON, pid = 23305

Solutions:

1) Find out the reason for the database or instance hang. Getting a global system state dump and global hang analyze output is critical when troubleshooting the database or instance hang issue. If the global system state dump can not be obtained, get the local system state dump from all instances around same time.
2) Check CHM (Cluster Health Monitor) output to see if the server had a CPU or memory load problem, network problem, or spinning lmd or lms processes.
3) Having OSWatcher output is helpful when CHM output is not available.

What happen before and after user Commit Fire in Oracle

Generally we use COMMIT after the completion of DML activity to make the changes permanently to disk.

The COMMIT statement ends a transaction successfully. 

Before the COMMIT statement is issued, the changes may not be visible to other transactions.
SQL> COMMIT;

 Before Commit: 

1. As we know when ever data maiplulation happens by DML queries, old values will be stored in undo tablespace

2. Oracle generates redo log entries in the redo log buffers in the SGA.
3. Oracle modifies the database buffers in the SGA.
 AFTER Commit:
 1.The transaction tables in the redo records are tagged with the unique system change number
(SCN) of the committed transaction.
2.The log writer writes the redo log information for the transaction from the redo log buffer to
the redo log files on disk, along with the transaction’s SCN. This is the point at which a commit
is considered complete in Oracle.

3.Any locks that Oracle holds are released, and Oracle marks the transaction as complete.

Administration of Votingdisk & OCR in 11G Rac

Clusterware :

Oracle Clusterware includes two major components Voting Disk & Oracle Clusterware registry
Voting Disk :
Voting disk maintains the node membership information by collecting the heartbeats of all nodes in the cluster periodically.
the voting disk must reside on shared storage for high availability and oracle clusterware enables multiple voting disks to overcome failures of voting disks.
CSSd is responsible for collecting the heartbeats and recording them in to the voting disk

What exactly Voting Disk consists of
-Node membership info
-Heartbeat info of all nodes in the cluster
-How many nodes in the cluster
-who is joining the cluster
-who is leaving the cluster

To find out the Voting Disk location
crsctl query css votedisk

To take the backup
From 11g onwards voting disk can be automatically backup along with OCR otherwise
dd if=currentloc of=backuploc

Adding, Deleting, or Migrating Voting Disks
$crsctl query css votedisk          #find out voting disk location and how many are disks are present
$crsctl replace votedisk {path_to_vote_disk}        #to replace the location ti filesystem
$crsctl replace votedisk +FRA                #to replace into ASM
$crsctl add css votedisk {path_to_vote_disk}        #To add one or more voting disks
$crsctl add css votedisk {path_to_new_votedisk_B}   #To replace voting disk A with voting disk B, you must add voting disk B, and then delete voting disk A.
$crsctl delete css votedisk {PATH|FUID}        #To remove a voting disk

Note:
If the cluster is down and cannot restart due to lost voting disks, then you must start CSS in exclusive mode by running the following command, as root:
#crsctl start crs -excl
After you start CSS in exclusive mode, you can replace the voting disk, as follows:
#crsctl replace votedisk path_to_voting_disk

Migrating voting disks to Oracle ASM
To migrate voting disks to Oracle ASM, specify the Oracle ASM disk group name in the following command:
$ crsctl replace votedisk +asm_disk_group
You can run this command on any node in the cluster.
Verifying the voting disk location
After modifying the voting disk, verify the voting disk location, as follows:
$ crsctl query css votedisk

Restoring Voting Disks
If all of the voting disks are corrupted, then you can restore them, as follows:
Run the following command as root from only one node to start the Oracle Clusterware stack in exclusive mode, which does not require voting files to be present or usable:
# crsctl start crs -excl
Run the crsctl query css votedisk command to retrieve the list of voting files currently defined, similar to the following:
$ crsctl query css votedisk
--  -----    -----------------                --------- ---------
##  STATE    File Universal Id                File Name Disk group
 1. ONLINE   7c54856e98474f61bf349401e7c9fb95 (/dev/sdb1) [DATA]

This list may be empty if all voting disks were corrupted, or may have entries that are marked as status 3 or OFF.
Depending on where you store your voting files, do one of the following:

    If the voting disks are stored in Oracle ASM, then run the following command to migrate the voting disks to the Oracle ASM disk group you specify:
    $crsctl replace votedisk +asm_disk_group
    The Oracle ASM disk group to which you migrate the voting files must exist in Oracle ASM. You can use this command whether the voting disks were stored in Oracle ASM or some other storage device.
    If you did not store voting disks in Oracle ASM, then run the following command using the File Universal Identifier (FUID) obtained in the previous step:
    $ crsctl delete css votedisk FUID
    Add a voting disk, as follows:
    $ crsctl add css votedisk path_to_voting_disk
Stop the Oracle Clusterware stack as root:
# crsctl stop crs

Note:
If the Oracle Clusterware stack is running in exclusive mode, then use the -f option to force the shutdown of the stack.

Restart the Oracle Clusterware stack in normal mode as root:
# crsctl start crs


OCR :
which maintains the cluster configuration information of all nodes in the cluster...like resources 
as well as clusterware database information.
it also maintains the information about processes that controls the Oracle Clusterware.
the OCR must reside on shared storage for accessibility to all nodes.
OCR can be replaced online.

What exactly OCR consists of
-Node Membership info
-Software active version
-the location of 11g voting disk
-server pool
-status of the cluster resources
-server,network,database,instance,listener up/down
-Configuration for the cluster resources such as RAC databases, listeners,databases,instances
-Cluster database instance to node mapping
-ASM instances & Disk Groups
-Clusterware resources like vip,services and their status info
-TAF policy,Load Balancing
-Finally About OCR backups

Who Updates OCR
-CSSd at the time of cluster setup--->to update the status of the servers
-CSS during node addition/Deletion
-CRSd about status of nodes during failures/reconfiguration
-SRVCTL,CRSCTL,OEM,NETCA,DBCA,DBUA,ASMCA


each node in the cluster maintains a copy of local OCR in memory for better performance.
and each node is required to update the OCR as required.
Oracle uses a distributed shared memory architecture at the time of cluster management to optimize queries against cluster repository.
Each node maintains a copy of the OCR in memory. Oracle Clusterware uses a background process to access the OCR cache. Only one CRSd process (designated as the master) in the cluster performs any disk read/write activity.

cat /etc/oracle/ocr.loc     #How to find OCR location

To show the backup Info of OCR
ocrconfig -showbackup auto
ocrconfig -showbackup manual

To take the backup manually
ocrconfig -manualbackup

Adding an OCR file
ocrconfig -add /backup/ocr         # In filesystem
ocrconfig -add +FRA           # In ASM

Moving the OCR to new location
ocrconfig -replace /backup/ocr -replacement +FRA

Removing an OCR
ocrconfig -delete +FRA

Repairing OCR
ocrconfig -repair -add /backup/ocr

Logical export of the OCR
ocrconfig -export /backup/ocr/ocr.bkp

To Change the backup location Of OCR
ocrconfig -backuploc /backup/ocr

Restoing OCR steps
#ocrconfig -showbackup auto    #Find out the Location
Stop crs on all nodes
#crsctl stop crs       #if this command fails use the below command
#crsctl stop crs -f   #use this if above command fails

Start CRS on one node in exclusive mode
#crsctl start crs -excl

check crsd is running...if it's running stop it
#crsctl stop resource ora.crsd -init

Restore the OCR
#ocrconfig -restore {location of the backup}

Verify the integrity of OCR
#ocrcheck

Stop the CRS that was started exclusively
#crsctl stop crs        #if this won't workout use below command
#crsctl stop crs -f        #for forcible stopping of crs

Start the CRS on all nodes one by one
#crsctl start crs

Check the integrity of newly restore OCR
#cluvfy comp ocr -n all -verbose

HighWaterMark-HWM in Oracle

All the Oracle Segments will have upper boundary congaing the data with in the segment.
the upper boundary is called HighWaterMark-HWM
The HWM is an indication that marks all blocks are allocated but not used yet.

Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. 

The HIGH WATER MARK is set at the beginning of the segment when the table is created. Deleting rows will never reset the HWM even if all the rows are deleted from the table. Only the TRUNCATE command will reset the HWM.

 The main disadvantage of this is that oracle always read the blocks up to high water mark in case of full table scan.  You may have ever notice that doing a count (*) on empty table, takes time to show you 0 rows.  The reason for delay is setting of HWM at higher position.

"Whenever optimizer takes full table scan, it scans all the blocks below HWM. This would degrade the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to reset the HWM."

  

HWMBLOCKS = “Blocks Containing data” + “Empty blocks”          HWMBLOCKS – “Blocks containing data” HWM % = (----------------------------------)  * 100                   HWMBLOCKS

You can also use DBMS_SPACE.UNUSED_SPACE procedure to determine HWM.

High Watermark = Total blocks - Unused blocks – 1

To view the high water mark of a particular table::
ANALYZE TABLE OWNER.TABNAME ESTIMATE/COMPUTE STATISTICS;

This will update the table statistics.
After generating the statistics,
to determine the high water mark:
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='tabname' ;

BLOCKS represents the number of blocks ‘ever’ used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the ‘HIGH WATER MARK’.
Deleting records doesn’t lower the high water mark. Therefore, deleting
records doesn’t raise the EMPTY_BLOCKS figure.
"Only Truncating resets the High Water Mark."

Ways to reset HWM :-

Method-1
Alter table  tab_name  move tablespace

This option requires rebuilding the indexes. Since the index will be invalid/unusable after running the above command.it'll be a overhead to the DBA to rebuild them

Method-2

1. Export the data
2. Truncate the table
3. Import the table
4. Analyze the table

 

Top Command Analyzing in linux environmetns

How do I determine CPU and Memory utilization, based on running processes in Linux using TOP?

Top command provides a real-time look at what is happening with your system. Top produces so much output that a new user may get over whelmed with all that’s presented and what it means.

Let’s take a look at TOP one line at a time.

The first line in top:

top - 22:09:08 up 14 min,  1 user,  load average: 0.21, 0.23, 0.30

“22:09:08″ is the current time; “up 14 min” shows how long the system has been up for; “1 user” how many users are logged in; “load average: 0.21, 0.23, 0.30″ the load average of the system (1minute, 5 minutes, 15 minutes).

Load average is an extensive topic and to understand its inner workings can be daunting. The simplest of definitions states that load average is the cpu utilization over a period of time. A load average of 1 means your cpu is being fully utilized and processes are not having to wait to use a CPU. A load average above 1 indicates that processes need to wait and your system will be less responsive. If your load average is consistently above 3 and your system is running slow you may want to upgrade to more CPU’s or a faster CPU.

The second line in top:

Tasks:  82 total,   1 running,  81 sleeping,   0 stopped,   0 zombie

Shows the number of processes and their current state.

The third line in top:

Cpu(s):  9.5%us, 31.2%sy,  0.0%ni, 27.0%id,  7.6%wa,  1.0%hi, 23.7%si,  0.0%st

Shows CPU utilization details. “9.5%us” user processes are using 9.5%; “31.2%sy” system processes are using 31.2%; “27.0%id” percentage of available cpu; “7.6%wa” time CPU is waiting for IO.

When first analyzing the Cpu(s) line in top look at the %id to see how much cpu is available. If %id is low then focus on %us, %sy, and %wa to determine what is using the CPU.

The fourth and fifth lines in top:

Mem:    255592k total,   167568k used,    88024k free,    25068k buffers

Swap:   524280k total,        0k used,   524280k free,    85724k cached

Describes the memory usage. These numbers can be misleading. “255592k total” is total memory in the system; “167568K used” is the part of the RAM that currently contains information; “88024k free” is the part of RAM that contains no information; “25068K buffers and 85724k cached” is the buffered and cached data for IO.

So what is the actual amount of free RAM available for programs to use ?

The answer is: free + (buffers + cached)

88024k + (25068k + 85724k) = 198816k

How much RAM is being used by progams ?

The answer is: used – (buffers + cached)

167568k – (25068k + 85724k) = 56776k

The processes information:

Top will display the process using the most CPU usage in descending order. Lets describe each column that represents a process.

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

3166 apache    15   0 29444 6112 1524 S  6.6  2.4   0:00.79 httpd

PID – process ID of the process

USER – User who is running the process

PR – The priority of the process

NI – Nice value of the process (higher value indicates lower priority)

VIRT – The total amount of virtual memory used

RES – Resident task size

SHR – Amount of shared memory used

S – State of the task. Values are S (sleeping), D (uninterruptible sleep), R (running), Z(zombies), or T (stopped or traced)

%CPU – Percentage of CPU used

%MEM – Percentage of Memory used

TIME+ – Total CPU time used

COMMAND – Command issued

Interacting with TOP

Now that we are able to understand the output from TOP lets learn how to change the way the output is displayed.

Just press the following key while running top and the output will be sorted in real time.

M – Sort by memory usage

P – Sort by CPU usage

T – Sort by cumulative time

z – Color display

k – Kill a process

q – quit

If we want to kill the process with PID 3161, then press “k” and a prompt will ask you for the PID number, and enter 3161.

Command Line Parameters with TOP

You can control what top displays by issuing parameters when you run top.

- d – Controls the delay between refreshes

- p – Specify the process by PID that you want to monitor

-n – Update the display this number of times and then exit

If we want to only monitor the http process with a PID of 3166

$ top -p 3166

If we want to change the delay between refreshes to 5 seconds

$ top -d 5

DataGuard Troubleshooting queries and parameters

Standby Database:

selectNAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL, CURRENT_SCN,FLASHBACK_ON,FORCE_LOGGING from v$database;

select inst_id,process, status, client_process, thread#, sequence#, block#, blocks  from gv$managed_standby  where process = 'MRP0';

Starting  MRP0 :

RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION PARALLEL 67;

For RAC use
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT FROM SESSION PARALLEL 132 USING CURRENT LOGFILE;

On Standby
select * from gv$active_instances;
ps -ef|grep -i mrp
select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;

Defer Log Shipping:
alter system set log_archive_dest_state_2=defer scope=both;
alter system set dg_broker_start=false;

Enable Log Shipping:
alter system set log_archive_dest_state_2 = 'enable';
alter system set dg_broker_start=true;

Starting the STANDBY DATABASE:
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;

Checking For Dataguard Errorr:
select to_char(timestamp,'DD/MM/YY HH24:MI:SS') timestamp,severity, message_num, message from v$dataguard_status where severity in ('Error','Fatal') order by timestamp;
select  *  from v$ARCHIVE_GAP;

Missing Logs on Standby:
select local.thread# , local.sequence# from (select thread# , sequence# from v$archived_log where dest_id=1) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#) ;

Starting MRP0:
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Stoping MRP0:
RECOVER MANAGED STANDBY DATABASE CANCEL;

MRP0 STATUS - RAC:
select inst_id,process, status, client_process, thread#, sequence#, block#, blocks from gv$managed_standby where process = 'MRPO';
select severity, error_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status;

Registring Logfile
alter database register logfile  ' 26457 ';

How To Check Oracle Physical Standby is in Sync with the Primary or Not? 
On Primary
set pages 1000
set lines 120
column DEST_NAME format a20
column DESTINATION format a35
column ARCHIVER format a10
column TARGET format a15
column status format a10
column error format a15
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest where DESTINATION is NOT NULL
/

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence generated"  FROM V$ARCHIVED_LOG  WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)  ORDER BY 1
/
select max(sequence#),thread# from gv$log group by thread#;

set numwidth 15
select max(sequence#) current_seq from v$log;
/
On Standby
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"  FROM  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL  WHERE  ARCH.THREAD# = APPL.THREAD#  ORDER BY 1;
/
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
/
select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;
/
select max(sequence#),thread# from gv$archived_log where applied='YES' group by thread#;
/
set numwidth 15
select max(applied_seq#) last_seq from v$archive_dest_status;
/

Check which logs are missing
Run this on the standby...
select local.thread#, local.sequence# from   (select thread#  ,  sequence#   from    v$archived_log   where dest_id=1)  local
where  local.sequence# not in  (select sequence#  from v$archived_log  where dest_id=2 and   thread# = local.thread#)
/
Display info about all log destinations
To be run on the primary
set lines 100
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
select ds.dest_id id, ad.status, ds.database_mode db_mode, ad.archiver type, ds.recovery_mode, ds.protection_mode, ds.standby_logfile_count "SRLs" , ds.standby_logfile_active active, ds.archived_seq# from v$archive_dest_status ds, v$archive_dest ad where ds.dest_id = ad.dest_id and ad.status != 'INACTIVE'  order by ds.dest_id
/
Display log destinations options
To be run on the primary
set numwidth 8 lines 100
column id format 99
select dest_id id , archiver, transmit_mode, affirm , async_blocks async, net_timeout net_time, delay_mins delay, reopen_secs reopen
, register,binding  from v$archive_dest order by dest_id;
/
MRP Speed
Set Linesize 400
Col Values For A65
Col Recover_start For A21
Select To_char(START_TIME,'Dd.Mm.Yyyy Hh24:Mi:ss') "Recover_start",To_char(Item)||' = '||To_char(Sofar)||' '||To_char(Units)||' '|| To_char(TIMESTAMP,'Dd.Mm.Yyyy Hh24:Mi') "Values" From V$Recovery_progress Where Start_time=(Select Max(Start_time) From V$Recovery_progress);

/

TIME IT TOOK TO APPLY A LOG
  select TIMESTAMP,completion_time "ArchTime",SEQUENCE#,round((blocks*block_size)/(1024*1024),1) "SizeM",round((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60,1) "Diff(sec)",round((blocks*block_size)/1024/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),1) "KB/sec", round((blocks*block_size)/(1024*1024)/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP)
OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),3) "MB/sec",
round(((lead(TIMESTAMP,1,TIMESTAMP) over (order by TIMESTAMP))-completion_time)*24*60*60,1) "Lag(sec)" from v$archived_log a, v$dataguard_status dgs where a.name = replace(dgs.MESSAGE,'Media Recovery Log ','') and dgs.FACILITY = 'Log Apply Services'
order by TIMESTAMP desc;
/

Problem: Recovery service has stopped for a while and there has been a gap between primary and standby side. After recovery process was started again, standby side is not able to catch primary side because of low log applying performance. Disk I/O and memory utilization on standby server are nearly 100%.

Solution:
1 – Rebooting the standby server reduced memory utilization a little.
2 – ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL8 DISCONNECT FROM SESSION;
In general, using the parallel recovery option is most effective at reducing recovery time when several datafiles on several different disks are being recovered concurrently. The performance improvement from the parallel recovery option is also dependent upon whether the operating system supports asynchronous I/O. If asynchronous I/O is not supported, the parallel recovery option can dramatically reduce recovery time. If asynchronous I/O is supported, the recovery time may be only slightly reduced by using parallel recovery.
3 -alter system Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096 scope = spfile;
Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096
When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.
4 – Kernel parameters that changed in order to reduce file system cache size.
dbc_max_pct 10 10 Immed
dbc_min_pct 3 3 Immed

remap_table parameter in datapump

remap_table Parameter of Data-Pump in Oracle 11g

Oracle 11g datapump provide a new feature remap_table command to remap the table data to new table name on target database.we can use the REMAP_TABLE parameter to rename entire tables.

Syntax :

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename .

In 10g datapump ,we use the REMAP_SCHEMA parameter to remap the schema name during the import or we use the FROMUSER and TOUSER parameters in the original import . There is no parameter to remap table names . This means that Import DataPump can only import data into a table with the same name as the original table.

If we have to import a table data having same structure into a database i.e, it is containing the table with same name then we have to perform it in two ways .

I.) Rename the original source table temporarily : 

II.) If the original source table cannot be rename then follow the below steps :

a.) import the dump into another schemas.

b.) rename the table name.

c.) Again export the table .

d.) Finally import the table name .

Remap_table allows us to rename tables during an import operation . Here is demo of the remap_table :

Here , we will create a table and take export of it and import it in the same schemas . In this scenario we have table name "test" and we will rename it as "newtest".

1.) Create a table  "test"

SQL> conn hr/hr@noida

Connected.

SQL> create table test(id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> insert into test values (3);

1 row created.

SQL> insert into test values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID

----------

         1

         2

         3

         4

2.) Export the table "test"

SQL> host expdp hr/hr@noida    dumpfile=hr_test.dmp    logfile=hrtestlog.log     tables=test

Export: Release 11.2.0.1.0 - Production on Fri May 27 11:20:43 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@noida dumpfile=hr_test.dmp logfile=hrtestlog.log tables=test

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "HR"."TEST"                                 5.031 KB       4 rows

Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for HR.SYS_EXPORT_TABLE_01 is:

  D:\APP\NEERAJS\ADMIN\NOIDA\DPDUMP\HR_TEST.DMP

Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 11:21:16

Since,we have the dump of the table "test". We import into hr schemas with new name  "newtest"

3.) Import the dump with remap_table Parameter

SQL>host impdp hr/hr@noida  dumpfile=hr_test.dmp logfile=imphrtestlog.log remap_table=hr.test:newtest

Import: Release 11.2.0.1.0 - Production on Fri May 27 11:22:11 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "HR"."SYS_IMPORT_FULL_04" successfully loaded/unloaded

Starting "HR"."SYS_IMPORT_FULL_04":  hr/********@noida dumpfile=hr_test.dmp logfile=imphrtestlog.log remap_table=hr.test:newtest

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "HR"."NEWTEST"                              5.031 KB       4 rows

Job "HR"."SYS_IMPORT_FULL_04" successfully completed at 11:22:25

Since the job is successfully completed .So we check the imported table i.e, "newtest"

SQL> select * from tab;

TNAME                                     TABTYPE                         CLUSTERID

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

COUNTRIES                               TABLE

DEPARTMENTS                         TABLE

EMPLOYEES                              TABLE

EMP_DETAILS_VIEW                VIEW

JOBS                                          TABLE

JOB_HISTORY                           TABLE

LOCATIONS                               TABLE

NEWTEST                                  TABLE

REGIONS                                    TABLE

SYS_IMPORT_FULL_01             TABLE

SYS_IMPORT_FULL_02             TABLE

SYS_IMPORT_FULL_03             TABLE

TEST                                            TABLE

13 rows selected.

SQL> select * from newtest;

        ID

----------

         1

         2

         3

         4

Note : Only objects created by the Import will be remapped. In particular, preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND