Pages

Saturday, December 10, 2016

Dataguard Broker # DMON

Anyway,  steps to configure dataguard broker >
pre req's #
> Add global database name in listener on primary and standby >
> set dg_broker_start=true > and make sure dg_broker_config_file1 & dg_broker_config_file2 are created >

Step 1 > 
Add global database name to listener to both source and target database > 

Source > 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db11g_dgmgrl.localdomain)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = DB11G)
    )
  )

Target > 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sbydb11g_dgmgrl.localdomain)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = SBYDB11G)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = delta)
    )
  )

>>> 
Step 2> on both primary and standby > 

sql > alter system set dg_broker_start=true scope=both;

SQL> show parameter dg_broker

NAME               TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1     string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1DB11G.dat
dg_broker_config_file2     string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2DB11G.dat
dg_broker_start             boolean TRUE

Step 3> Register both primary and standydatabase and Enable configuration  > 

[oracle@dg1 admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> 
DGMGRL> conn sys       
Unrecognized command "conn", try "help"
DGMGRL> connect sys/oracle@TO_DB11G
Connected.
DGMGRL> 

>>>

DGMGRL> create configuration 'BROKER_P' as primary database is 'DB11G' connect identifier is 'TO_DB11G';
Configuration "BROKER_P" created with primary database "DB11G"

>>>> 

DGMGRL> add database 'SBYDB11G' as connect identifier is 'TO_SBYDB11G' maintained as physical;
Database "SBYDB11G" added
DGMGRL> show configuration;
Configuration - BROKER_P
  Protection Mode: MaxPerformance
  Databases:
    DB11G    - Primary database
    SBYDB11G - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

>>>> 

DGMGRL>  enable configuration;
Enabled.


>>>>

---- Nikhil Tatineni----
how about those chiefs |||||

Tuesday, December 6, 2016

Query's Used to monitor Goldengate Integrated Extract and replicat

Integrated capture GoldenGate views on Source Database >

col CAPTURE_NAME for a20;
col QUEUE_NAME for a15;
col START_SCN for 9999999999;
col STATUS for a10;
col CAPTURED_SCN for 9999999999;
col APPLIED_SCN for 9999999999;
col SOURCE_DATABASE for a10;
col LOGMINER_ID for 9999999;
col REQUIRED_CHECKPOINTSCN for a30;
col STATUS_CHANGE_TIME for a15;
col ERROR_NUMBER for a15;
col ERROR_MESSAGE for a10;
col CAPTURE_TYPE for a10;
col START_TIME for a30

SELECT CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS,
CAPTURED_SCN, APPLIED_SCN, SOURCE_DATABASE,
LOGMINER_ID, REQUIRED_CHECKPOINT_SCN,
STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE,
CAPTURE_TYPE, START_TIME
FROM DBA_CAPTURE;

> dynamic statistics from the GoldenGate views 
col state for a30;
SELECT sid, serial#, capture#, CAPTURE_NAME, STARTUP_TIME, CAPTURE_TIME,
state, SGA_USED, BYTES_OF_REDO_MINED,
to_char(STATE_CHANGED_TIME, 'mm-dd-yy hh24:mi') STATE_CHANGED_TIME
FROM V$GOLDENGATE_CAPTURE;

>>>>

col capture_message_create_time for a30;
col enqueue_message_create_time for a27;
col available_message_create_time for a30;

SELECT capture_name,
to_char(capture_time, 'mm-dd-yy hh24:mi') capture_time,
capture_message_number,
to_char(capture_message_create_time ,'mm-dd-yy hh24:mi') capture_message_create_time,
to_char(enqueue_time,'mm-dd-yy hh24:mi') enqueue_time,
enqueue_message_number,
to_char(enqueue_message_create_time, 'mm-dd-yy hh24:mi') enqueue_message_create_time,
available_message_number,
to_char(available_message_create_time,'mm-dd-yy hh24:mi') available_message_create_time
FROM GV$GOLDENGATE_CAPTURE;

>>>

Monitor the number of open transactions and LCRs for each capture processes >

SELECT component_name capture_name, count(*) open_transactions,
sum(cumulative_message_count) LCRs
FROM GV$GOLDENGATE_TRANSACTION
WHERE component_type='CAPTURE'
group by component_name;

>>> >>> 

 LogMiner views for LogMiner sessions and statistics > 

col db_name for a15;

select INST_ID, SESSION_ID,SESSION_NAME,SESSION_STATE, DB_NAME,
NUM_PROCESS,START_SCN,END_SCN,SPILL_SCN, PROCESSED_SCN, PREPARED_SCN,
READ_SCN MAX_MEMORY_SIZE,USED_MEMORY_SIZE PINNED_TXN, PINNED_COMMITTED_TXN
from GV$LOGMNR_SESSION;
SELECT SESSION_ID, NAME, VALUE
FROM V$LOGMNR_STATS;

>>
Integrated Capture >

SQL> select CAPTURE_NAME, QUEUE_NAME, STATUS from DBA_CAPTURE;
SQL> select OWNER, QUEUE_TABLE, QUEUE_TYPE from dba_queues where NAME='OGG$Q_TESTEXT1';


Integrated Replicat >

SQL> select REPLICAT_NAME,SERVER_NAME from DBA_GOLDENGATE_INBOUND;
SQL> select APPLY_NAME, QUEUE_NAME, status from dba_apply;
SQL> select apply_name,state from V$GG_APPLY_COORDINATOR ;


—Oracle In-Memory—

Thursday, November 17, 2016

Keygen # GoldenGate Password Encryption

Advantages # 
password protection 
Trails can be encrypted 

> PROCEDURE to implement > 
using "keygen" utility, we can generate hex keys 
after Enter the hex keys in ASCII file in goldengate home directory and encrypt the password 

Step 1# create hex keys using keygen utility # keygen utility exists in goldengate home; key length is max 256 bits 
Syntax # ./keygen <key_length> <Number of keys> 

[oracle@tnc1 home]$ ./keygen 128 4
0x05A5A84484FA4824B0BF2528ADC0E461
0xD392635258E8AC4D0C417D054988F659
0xA1801E602CD6107767C2D462E54F0852
0x6F6ED96D01C47420C3432C4081171A4A

Step 2 # Enter all 4 keys in ASCII file ENKEYS and < Name key value with key Name as follows >

[oracle@tnc1 home]$touch ENCKEYS 

[oracle@tnc1 home]$ cat ENCKEYS 
# Key name      key Value 
key128_1        0x05A5A84484FA4824B0BF2528ADC0E461
key128_2        0xD392635258E8AC4D0C417D054988F659
key128_3        0xA1801E602CD6107767C2D462E54F0852
key128_4        0x6F6ED96D01C47420C3432C4081171A4A

Usually we encrypt the ggsuser password with <encrypt_type>
encrypt_type = AES128, AES192,  AES256
Syntax # 
GGSCI> encrypt password <GOLDENGATE Password> <encrypt_type> ENCRYPTKEY <keyname> 

GGSCI (tnc1.localdomain) 2> encrypt password oracle aes128 encryptkey key128_2
Encrypted password:  AADAAAAAAAAAAAGAKHGAGDRIUAZDVENBDIJHSCIEVDFJCAFJUBXIQDEDXHLHIBHJPICCJILGSEOATIJJ
Algorithm used:  AES128

Testing database login from goldengate command line utility # ggsci >
GGSCI (tnc1.localdomain) 5> dblogin userid ggsuser password AADAAAAAAAAAAAGAKHGAGDRIUAZDVENBDIJHSCIEVDFJCAFJUBXIQDEDXHLHIBHJPICCJILGSEOATIJJ, encryptkey key128_2
Successfully logged into database.

STEP3 # copy ENKEYS file to source and Target goldengate homes  # 

--Nikhil Tatineni--
--Oracle in memory---



finding Culprit SQL in RAC cluster

Following Query’ Tells about > SQL Query's in RAC which are waiting to get CPU (or) Query is waiting on CPU > 
Depend on result of following query 
we will dive in and find set of sql’s which is waiting to get CPU 
or which is waiting on CPU :) 

SQL> select * from (
select
SQL_ID ,
sum(decode(session_state,'ON CPU',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) - 
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),
0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User 
I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
from v$active_session_history
where SQL_ID is not NULL
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1))
)
where rownum <11;

following .......
Run following Query to get wait events on database and match Query is waiting on CPU 
SQL> SELECT DISTINCT wait_class FROM gv$event_name ORDER BY 1;

Get wait_class from gv$event_name & replace wait_class and find out culprit SQL on database >
SQL> SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, v$event_name evt
WHERE ash.sample_time > SYSDATE - 3/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'System I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

From gv$active_session_history" we will get sql_id and helps to check execution plan of sql statement :) and Investigate further to decrease waits on cluster 

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('xxxxxxxxxxxx'));

---Nikhil Tatineni---

---Oracle Database Admin --- 

Thursday, November 10, 2016

Remove DDL replication # GoldenGate

Removing DDL replication from GoldenGate >
[oracle@red goldengate]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 2 22:16:31 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> @ddl_disable
Trigger altered.

SQL> @ddl_remove.sql
DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggsuser
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script complete.

SQL> @marker_remove.sql
Marker removal script.
WARNING: this script removes all marker objects and data.
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggsuser
PL/SQL procedure successfully completed.
Sequence dropped.
Table dropped.
Script complete.

DDL replication  dropped successfully <<


---Nikhil Tatineni--
--GoldenGate--

Replicat struck on trail # goldengate

Scenario # Replicat struck on trail  and data is out of sync with source and target 
> Root cause > Replicat unable to perform " ETROLLOVER "

GGSCI (tnc0624) 5> info REP1
REPLICAT   REP1  Last Started 2016-11-06 08:07   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026
                     2016-11-06 08:21:23.805171  RBA 1534

GGSCI (tnc0624) 6> stats REP2
Sending STATS request to REPLICAT REP2 ...
No active replication maps.

GGSCI (tnc0624) 7> stats REP1
Sending STATS request to REPLICAT REP1 ...
No active replication maps.


GGSCI (tnc0624) 8> sh ls -ltr /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8*
-rw-rw-rw- 1 ggs01g ggs01g 399999709 Nov  3 15:30 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000018
-rw-rw-rw- 1 ggs01g ggs01g  19982829 Nov  4 04:46 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000019
-rw-rw-rw- 1 ggs01g ggs01g 174809829 Nov  6 07:53 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000020
-rw-rw-rw- 1 ggs01g ggs01g      1289 Nov  6 07:57 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000021
-rw-rw-rw- 1 ggs01g ggs01g      1338 Nov  6 08:03 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000022
-rw-rw-rw- 1 ggs01g ggs01g      1387 Nov  6 08:09 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000023
-rw-rw-rw- 1 ggs01g ggs01g      1436 Nov  6 08:15 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000024
-rw-rw-rw- 1 ggs01g ggs01g      1485 Nov  6 08:21 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000025
-rw-rw-rw- 1 ggs01g ggs01g      1534 Nov  6 08:21 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026
-rw-rw-rw- 1 ggs01g ggs01g   1119647 Nov  6 11:17 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000027

Resolution # 
GGSCI (tnc0624) 8> info REP1
REPLICAT   REP1  Last Started 2016-11-06 08:07   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026
                     2016-11-06 08:21:23.805171  RBA 1534

-rw-rw-rw- 1 ggs01g ggs01g      1534 Nov  6 08:21 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000026
-rw-rw-rw- 1 ggs01g ggs01g   1119647 Nov  6 11:17 /opt/app/ggs01/ggs/11.2.1.0.3/trails/m8000027

Here it is unable to rollover from trail sequence number 26 to 27 :) 
perform ETROLLOVER >

GGSCI (tnc0624) 9> stop REP1
GGSCI (tnc0624) 10> stop REP2

GGSCI (tnc0624) 11> alter REP1, extseqno 27 extrba 0 
GGSCI (tnc0624) 12> alter REP2, extseqno 27 extrba 0 

GGSCI (tnc0624) 13> start REP1

--Nikhil Tatineni--
--Oracle GoldenGate--

Thursday, October 13, 2016

Media Recovery terminated with error 28365 # TDE

>>> >>>> >>>> >>>
scenario # created Oracle Wallet <<
>> MRP stopped on standby # 

SQL> select * from v$dataguard_status where severity in ('Error','Fatal') and timestamp > (sysdate -1);
FACILITY SEVERITY   DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP
------------------------ ------------- ------------------------- --------
MESSAGE
-------------------------------------------------------------------------
Log Apply Services Error 0   4         28365 YES  13-OCT-16
MRP0: Background Media Recovery terminated with error 28365

SQL> select * from v$encryption_wallet;
WRL_TYPE  WRL_PARAMETER  STATUS
————————————————————————————————
file     /u02/encrypt/    CLOSED

Resolution # >>>> 

Steps to perform to resolve Issue on standby >>>
shutdown the standby database 
scp cwallet.sso & ewallet.p12 to standby server wallet location from primary 
Mount the database 
Start MRP process 

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

From Primary > scp cwallet.sso and ewallet.p12 
[oracle@dg1 encrypt]$ scp * oracle@192.168.56.104:/u02/encrypt/
oracle@192.168.56.104's password: 
cwallet.sso                                   100% 2923     2.9KB/s   00:00    
ewallet.p12                                   100% 2845     2.8KB/s   00:00 

SQL> startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size     2257840 bytes
Variable Size   541068368 bytes
Database Buffers   289406976 bytes
Redo Buffers     2371584 bytes
Database mounted.

SQL> select * from v$encryption_wallet;
WRL_TYPE  WRL_PARAMETER  STATUS
————————————————
file     /u02/encrypt/    OPEN

> Start the MRP on standby< 
SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select process from v$managed_standby where process like 'MRP%';
PROCESS
---------
MRP0

>>> 

--Nikhil Tatineni--
--Dataguard-- 
Links Related to Datagaurd <
Applying logs with delay # Standby database #
http://www.oracleinmemory.com/2016/07/dataguard-delay-parameter.html

Physical Standby # Active Dataguard 11g
http://www.oracleinmemory.com/2016/07/physical-standby-active-dataguard-11g.html

Snapshot standby # 11g
http://www.oracleinmemory.com/2016/07/dataguard-converting-physical-standby.html

Troubleshooting #

Dataguard # oldest online log sequence 0
http://www.oracleinmemory.com/2016/07/dataguard-oldest-online-log-sequence-0.html

ARBx # Slave Process in ASM

DBA manage databases on ASM & manages ASM disk groups.  When we notice  the disk group holding database is filled, DBA will add Brand new disk to existing disk group. Here I want to elaborate the background process of ASM i.e (ARBx), How ASM will perform rebalancing operation? ASM allocate ARBx( Actual Rebalancing process ) to perform rebalancing operation inside the disk group. ARBx is responsible for data movement between disks inside the disk group. The number of rebalancing process allocated depend on ASM_POWER_LIMIT value. Based on ASM disk group compatibility, we can alter ASM_POWER_LIMIT
if disk group asm  compatibility >= 11.2.0.2 (  alter asm_power_limit from 0 to 1024) 
if disk group asm compatibility < 11.2.0.2 ( alter asm_power_limit from 0 to 11) 

Query to check ASM disk group compatibility using v$asm_diskgroup
col COMPATIBILITY form a10
SQL> col DATABASE_COMPATIBILITY form a10
SQL> col NAME form a20
SQL> select group_number, name,
compatibility, database_compatibility from v$asm_diskgroup;

 Regularly when we will add disk to the disk group by logging into SYSASM (or) using ASMCA 
sql> conn / as sysasm
sql> alter diskgroup stage add disk '/dev/asm-disk8','/dev/asm-disk9';
sql>alter diskgroup DISKGROUP rebalance power 5;
when you say 5, 5 actual rebalancing (ARBx) process are allocated inside the diskgroup to improve performance we can alter this number to higher value, where 0 will disable the Rebalancing process

Query performance of ASM_ Rebalancing operation can be monitored using "v$asm_operation"
 SQL> select group_number,operation,state,power,est_minutes from v$asm_operation;

Note > It is always recommended to add a disk to existing disk group during off hours, rebalancing operation will impact the performance of database 

Adding a disk to diskgroup > ASM

Wednesday, October 12, 2016

Friendly DBA: Sample Datapump Export and Import Scripts

Based on requirement, modify  Datapump Export and Import scripts 

[oracle@dg1 export]$ cat exp_oxford_schema.sh
TS_EXPORT=/u01/app/export
PWD=oracle
LOGFILE=exp_schema_oxford.log
ST=`date`
echo $PWD | expdp system directory=db_export  dumpfile=exp_schema_oxford.dmp  content=all logfile=$LOGFILE schemas=oxford job_name=exp_oxford
ET=`date`
echo "Start Time=$ST" >>$LOGFILE
echo "End Time=$ET" >>$LOGFILE

[oracle@dg2 export]$ cat impdp_oxford.sh 
DB_EXPORT=/u01/app/export
PWD=oracle
LOGFILE=imp_schema_oxford.log
ST=`date`
echo $PWD | impdp system directory=db_export  dumpfile=exp_schema_oxford.dmp content=all logfile=$LOGFILE schemas=oxford job_name=impdp_oxford remap_tablespace=oracle_tb:delta_tb
ET=`date`
echo "Start Time=$ST" >>$LOGFILE
echo "End Time=$ET" >>$LOGFILE

run your scripts in background using nohup  for example 
$ nohup ./impdp_oxford.sh & 

--Nikhil Tatineni--
--Datapump -- 

enable enable_goldengate_replication from 11.2.0.4

>> >>Scenario # Tried to enable supplemental logging at schema level on 11.2.0.4 database triggered with following error << << 

GGSCI (dg1.localdomain) 2> dblogin userid ggsuser password oracle
Successfully logged into database.

GGSCI (dg1.localdomain as ggsuser@DB11G) 3> add schematrandata oxford
ERROR: Operation not supported because enable_goldengate_replication is not set to true.

Resolution #  logged into the database, enabled parameter 

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

* Note > If you are upgrading database to 11.2.0.4. make sure you enable parameter  "enable_goldengate_replication=true"  to support goldengate replication 

>> >> again it's failed with error >

GGSCI (dg1.localdomain as ggsuser@DB11G) 3> add schematrandata oxford

2016-10-10 12:50:38  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema oxford because of the following SQL error: ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_CAPTURE_ADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('oxford','ALLKEYS_ON'); END;.

Root cause: ggsuser doesn't have enough privileges to run package 
Resolution: logged into the database, grant dba to ggsuser during installation  

sql> grant dba to ggsuser;

>>>>
Nikhil Tatineni--
Oracle DBA -- 

Monday, October 10, 2016

Query's to Monitor Standby Database



Query to check apply lag and transport lag 
Apply lag # is difference in elapsed time between last applied change become visible on standby and when change was first visible on primary 
Transport lag # Amount of data i.e standby has not  received from primary 
>>> >> view # v$dataguard_stats
select name,value,time_computed,datum_time from v$dataguard_stats where name=’%lag’;

Query to check errors triggered in alert log file and trace file
>>> >> view # v$dataguard_status 
select * from v$dataguard_status where 
severity in ('Error','Fatal') and 
timestamp > (sysdate -1);

Query to check Media Recovery Process is currently running
>>> >> view # v$managed_standby
select * from v$managed_standby where process like 'MRP%';

Query to verify logs that are shipped and applied to standby database 
>>> >> view # v$archived_log
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Query to check missing archive logs i.e not shipped to 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#);

Query to Verify that the last sequence# received and the last sequence# applied to standby database
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;

Query to check log file gap between primary and standby and dest status 
>> View v$Archive_Dest_status
select status, gap_status from v$Archive_Dest_status where dest_id = 2;

Query to check what are the archive logs missing on standby database 
>> View  v$Archive_gap 
select * from v$Archive_gap;
( if this query result's output > 
check missing exists on primary , 
manually copy it and 
register the logs 
and start MRP) 

Saturday, October 8, 2016

Procedure to set old password

setting password > 
If database user 'oxford' or application user password is expired and he want's to use old password 
Procedure as follows 

SQL> select SPARE4 from sys.user$ where name='OXFORD';
SPARE4
--------------------------------------------------------------------------------
S:75C6294AF9823AF42A8BC83BE2FB03A0727988159F34241B4211E71FC1AB

SQL> alter user oxford identified by values 'S:75C6294AF9823AF42A8BC83BE2FB03A0727988159F34241B4211E71FC1AB';
User altered.

SQL> conn oxford/oracle;

Connected.

--Nikhil Tatineni--
--Oracle--

Saturday, September 17, 2016

oratop # DBA friendly

The “oratop” displays relevant database activity information presented in four sections. 

Section 1 - HEADER: Global database information  
Section 2 - DATABASS: Database instance Activity  
Section 3 - DB EVENTS: AWR like “Top 5 Timed Events“  

Section 4 – PROCESS/SQL: Processes/SQL information Specific detail of the various sections data can be obtained using the interactive help (see Runtime usage Sections Detail below) 


[oracle@tnc2 ~]$ tfactl print status
.-------------------------------------------------------------------------------------------.
| Host | Status of TFA | PID  | Port | Version    | Build ID             | Inventory Status |
+------+---------------+------+------+------------+----------------------+------------------+
| tnc2 | RUNNING       | 4280 | 5000 | 12.1.2.8.0 | 12128020160623222219 | COMPLETE         |
'------+---------------+------+------+------------+----------------------+------------------'
[oracle@tnc2 ~]$ tfactl
tfactl> db12c                                                                                        

Usage : /u01/app/12.1.0.2/grid/bin/tfactl <command> [options]
<command> =
          print          Print requested details
          purge          Delete collections from TFA repository
          analyze        List events summary and search strings in alert logs.
          diagcollect    Collect logs from across nodes in cluster
          collection     Manage TFA collections
          directory      Add or Remove or Modify directory in TFA
          toolstatus     Prints the status of TFA Support Tools
          run <tool>     Run the desired support tool
          start <tool>   Starts the desired support tool
          stop <tool>    Stops the desired support tool

For help with a command: /u01/app/12.1.0.2/grid/bin/tfactl <command> -help

tfactl> db db12c                                                                                     
Set db to DB12C
DB12C tfactl> oratop -i 10 -bn10                                                                     
Cycle 1 - oratop: Release 14.1.2 Production on Wed Sep 14 11:00:16 2016

Oracle 12c - db1 11:00:08 up: 0.4h,   2 ins,    0 sn,   0 us, 1.8G mt,  1.9% db
ID %CPU LOAD %DCU   AAS  ASC  ASI  ASW  AST IOPS %FR   PGA UTPS UCPS SSRT  %DBT
-------------------------------------------------------------------------------
 1   21    1    0     1    0    0    0    0    4   6  368M    0    2 975u  84.3
 2   11    1    0     1    0    0    0    0    3   4  323M    0    2 462u  15.7

EVENT (C)                        TOT WAITS   TIME(s)  AVG_MS  PCT    WAIT_CLASS
----------------------------------------------------------------------------------------------
PX Deq: Slave Session Stats           2760         453   179.6   36           Other
PX Deq: reap credit                       47402       320     6.7     25         Other
DB CPU                                             188        15              
oracle thread bootstrap                     319       183   580.5   15        Other
reliable message                               890       118   168.9    9          Other
ID   SID     SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T
-------------------------------------------------------------------------------

From 
Section 4 – PROCESS/SQL: Processes/SQL information Specific detail of the various sections data 
we will get SQL_ID involved in waits 

check execution plan of SQL statement using dbms_xplan.display_cursor & SQL_TEXT as follows
sql> select * from table(dbms_xplan.display_cursor(‘&sql_id’,’&child_no’,’typical’));

SQL> col SQL_TEXT format a10000
SQL> select SQL_TEXT from v$sqltext where SQL_ID='worldoforacle';

--Nikhil Tatineni--
--RAC--