ORA-600 [kwqitnmphe:ltbagi], [1], [0] reported in the alert log file.

ORA-00600 [kwqitnmphe:ltbagi]

Cause: This issue arises in 12.1.0.2. The error occurs because there are still Historical Messages without a current Subscriber (a.k.a. orphaned entries in the History table), and can be confirmed by the following SQL statement:

select
sub_hist.NAME, sub_hist.SUBSCRIBER#, sub_hist.MSGID, msg.QUEUE, msg.MSG_STATE
from
AQ$_SYS$SERVICE_METRICS_TAB_H sub_hist,
AQ$SYS$SERVICE_METRICS_TAB msg
where
sub_hist.MSGID = msg.MSG_ID and
NOT EXISTS (select sub.SUBSCRIBER_ID from aq$_SYS$SERVICE_METRICS_TAB_s sub where sub_hist.subscriber# = sub.SUBSCRIBER_ID)
;

There are several bugs reported to cause this issue:
Bug 17831758: ORA-600 [kwqitnmphe:ltbagi] in Qnnn background process
Bug 18536720: ORA-600 [kwqitnmphe:ltbagi] processing History IOT in AQ
Bug 16204151: -ORA-600 [kwqitnmphe:ltbagi] when subscriber is dropped pending enqueue/dequeue
However, even with the fix for these bugs in place the ORA-00600 [kwqitnmphe:ltbagi] errors are still seen because the problem data still exists in the queue table.

Solution:
1.  Do the following to purge the data in the queue table and IOTs

connect / as sysdba

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
  queue_table => 'SYS.SYS$SERVICE_METRICS_TAB',
  purge_condition => NULL,
  purge_options => po);
END;
/

The data in this queue table is transitory, so although it is lost it will soon be replaced.


2.  Apply the fixes for bug 17831758, bug 18536720 and bug 16204151 and ensure that post-install steps are followed.

All bugs will be resolved in 12.2 when it is available.
Recompile all invalid objects

@?/rdbms/admin/utlrp.sql

ORA-01105: mount is incompatible with mounts by other instances ORA-19808: recovery destination parameter mismatch

ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch


Cause: it occurs in RAC environment. issue is due to mismatch of DB_RECOVERY parameters between nodes.
Solution: Make sure the parameters db_recovery_file_dest,db_recovery_file_dest_size are same for all nodes

Example:

Node-1
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0

Node-2
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 100G
recovery_parallelism integer 0

SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 1694500024 bytes
Database Buffers 2566914048 bytes
Redo Buffers 12132352 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch

to clear this error and mount the database set  db_recovery_file_dest_size same on both nodes.
SQL> alter system set db_recovery_file_dest_size = 100G scope=spfile;
 System altered.

SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 1694500024 bytes
Database Buffers 2566914048 bytes
Redo Buffers 12132352 bytes
Database mounted.
Database opened. 

ORA-01264: Unable to create datafile file name ORA-19800: Unable to initialize Oracle Managed Destination

ORA-01264: Unable to create datafile file name
ORA-19800: Unable to initialize Oracle Managed Destination

Error Description:
----------------------------------
 ERROR at line 1:
ORA-01264: Unable to create datafile file name
ORA-19800: Unable to initialize Oracle Managed Destination
Solaris-AMD64 Error: 13: Permission denied

Or in linux the error stuck is,

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01264: Unable to create logfile file name
ORA-19800: Unable to initialize Oracle Managed Destination
Linux Error: 13: Permission denied

Cause of The problem:
-------------------------------------
The user don't have the permission to write in the specified location. This is Operating system permission. I got this error while duplicating the database from from backup location. in my case the exact cause is I forgot to mention the db_recovery_file_dest parameter.

Solution of The problem:
----------------------------------
Give the necessary permission to the user so that OS user who is running oracle can create file in the specified directory. or put the db_recovery_file_des in the duplicate command

First Scenario:
------------------------------------------
 duplicate target database to 'TARGET_1' from active database
spfile
set db_file_name_convert '/u01/dba/db/data/SOURCE_1','/u02/TARGET_1/db/data/TARGET_1'
set LOG_FILE_NAME_CONVERT '/u01/dba/db/data/SOURCE_1','/u02/TARGET_1/db/data/TARGET_1'
set control_files '/u02/TARGET_1/db/data/TARGET_1/control01.ctl','/u02/TARGET_1/db/fast_recovery_area/TARGET_1/control02.ctl'
set diagnostic_dest '/u02/TARGET_1/db'
set db_recovery_file_dest '/u02/TARGET_1/db/fast_recovery_area'
set audit_file_dest '/u02/TARGET_1/db/admin/TARGET_1/adump'
SET log_archive_dest_1 'location=/u02/TARGET_1/db/arch'
;


Second Scenario:
--------------------------------
In the second scenario user don't have permission on flash recovery area.
You can see flash recovery area location by,
SQL> show parameter db_recovery

# chown -R oracle:dba /u02/TARGET_1/db/fast_recovery_area


Now try to do operation and hopefully it will work.

Archive logs missing on standby site. DR is not in sync with production.

Problem: What to do when archive logs were missed on primary,standby site and standby is behind?

Solution: Take incremental backup from PROD and apply it on standby site



Standby site
select to_char(current_scn) from v$database;

Primary site:
run{
allocate channel t10 type disk format '/BKP_LOCATION/ORA_rman_incr_backup_PRODDB_%U.bck' maxpiecesize 5120m;
backup incremental from scn 262143096188 database tag = INCR_BKP_STBY;
release channel t10;
}
alter database create standby controlfile as '/tmp/standby.ctl';

Move the above stand by control file and incremental backup to stand by site.
Standby site:
rman target /
RMAN>restore controlfile from '/STNDBY_LOC/bkp/standby.ctl';
SQL> alter database mount;
rman target /
RMAN>catalog start with 'STNDBY_LOC/bkp/';
RMAN>recover database noredo;
SQL>alter database recover managed standby database disconnect from session;

DB installation GUI

Go for database installation using below steps:

install below libraries:

binutils-2.15.92.0.2

compat-libstdc++-33-3.2.3

compat-libstdc++-33-3.2.3 (32 bit)

elfutils-libelf-0.97

elfutils-libelf-devel-0.97

expat-1.95.7

gcc-3.4.6

gcc-c++-3.4.6

glibc-2.3.4-2.41

glibc-2.3.4-2.41 (32 bit)

glibc-common-2.3.4

glibc-devel-2.3.4

glibc-headers-2.3.4

libaio-0.3.105

libaio-0.3.105 (32 bit)

libaio-devel-0.3.105

libaio-devel-0.3.105 (32 bit)

libgcc-3.4.6

libgcc-3.4.6 (32-bit)

libstdc++-3.4.6

libstdc++-3.4.6 (32 bit)

libstdc++-devel 3.4.6

make-3.80

pdksh-5.2.14

sysstat-5.0.5

unixODBC-2.2.11

unixODBC-2.2.11 (32 bit)

unixODBC-devel-2.2.11

unixODBC-devel-2.2.11 (32 bit)

Go to /u01/dba/softwares/database path and follow the GUI same as below. this is the path where i unzipped software.

Action: Click ‘YES’ and proceed next.

Action: select “install database software only” and proceed next

 Action: select the “Real Application cluster database installation” and proceed further
You may check the SSH connectivity if you want. If it success it will move further or else will fail. Make sure connectivity should be password less connectivity

 if it is not a RAC database, just select the host name alone. no need to check connectivity

select language

Action: select Enterprise Edition and proceed further

Action: select the oracle base and Home values and proceed next

Action: check the admin,oper groups and proceed further

 Note: make sure all the prechecks are success before installation

Action: click finish to complete the activity. You can save the response file if you want. It will contain installation structure

Action: execute the above script as root user and make sure it run with no error

Action: click close button.
DATABSE installation completed….!!!!!!!

RAC installation with GUI

./runInstaller

 Action: select the 1st option “install and configure grid infrastructure for a cluster “ and click next

Action: select the “Advanced installation” option and proceed to next step

 select language "English"

Action: add your cluster name here and add the SCAN name. unselect the “Configure DNS”. Keep the port as 1521

Action: click the add button and add your second node details. And click next.

You can check the SSH connectivity between the nodes with “SSH Connectivity” button shown above. Enter the “oracle” user password in the password field. 

Check your public and private subnet values and click next

Action: select the “ASM” option if your storage is ASM type.

Action: select the OCR_VOTE_DISKs for creating voting disk location

Action: select the password for ASM sys user and proceed to next

Action: do not select the IPMI option and proceed next

Action: click next

 Action: decide the Oracle Base and Grid home and enter here in the fields and proceed next

  Action: Enter the Inventory directory
Now the pre-checks will start

 Action: make sure everything is succeeded and click next

 Action: save the response file. It consists of all the params set ready for installation.

 Action: the above scripts need to be executed as root user in both nodes. Execute the scripts one after another on each node. Don’t execute simultaneously.
Click OK, once scripts are executed by root user
Note: make sure the scripts give success response. It should not fail. If it is failed analyze the issue and apply the patch 9974223 for 11.2.0.2.

 Note: everything here should be success

Action: click close to close the terminal

GRID Installation is completed….!!!!!!!!!

What is logical backup?

What is logical backup?

Logical backup is nothing but taking backup of logical objects (tables, views, indexes, Constraints, stats etc) into a dump file.

There are 2 methods to perform logical backup.

1.EXPDP/IMPDP

2.EXP/IMP

EXPDP is introduced in Oracle 10g version whereas EXP is existing from previous oracle versions.

Logical backup can be prformed for tables,indexes,schemas,tablespaces and complete database. Below are the examples for performing logical backups.

Full Database Export

expdp SYSTEM/password directory=export_dir full=y dumpfile=exp_fulldb.dmp logfile=exp_fulldb.log

exp SYSTEM/password file=full.dmp log=full.log full=y

Schema Level Export

expdp SYSTEM/password  directory=export_dir schemas=scott,test dumpfile=exp_schemadb.dmp logfile=exp_schemadb.log

exp SYSTEM/password  USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp

Table Level Export

expdp SYSTEM/password  directory=export_dir tables=scott.emp,test.bonus dumpfile=exp_table.dmp logfile=exp_table.log

exp SYSTEM/password  USERID=scott/tiger TABLES=(scott.emp,scott.sales) FILE=exp_tab.dmp

Now move this dumpfiles to target database, place in import_dir directory and import the data.

Once the data is exported it will be placed in directory export_dir with given filenames. the export status can be seen in log files.

Schema Level Import

impdp SYSTEM/password  directory=import_dir schemas=app_schema,testschema dumpfile=imp_schemadb.dmp logfile=imp_schemadb.log

imp SYSTEM/password  USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp

Table Level Import

impdp directory=import_dir tables=scott.emp,test.bonus dumpfile=imp_tabledb.dmp logfile=imp_fulldb.log

imp SYSTEM/password FIlE=exp_tab.dmp  TABLES=(dept,emp)

Full Database Import

impdp directory=import_dir full=y dumpfile=exp_fulldb.dmp logfile=imp_fulldb.log

imp SYSTEM/password  FULL=y FIlE=full.dmp log=full.log