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 |||||

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—

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 --- 

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--

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 

Related Links to ASM # 
Adding disk to existing diskgroup 
http://www.oracleinmemory.com/search?q=adding+disk+to+diskgroup
ACFS RAC ASM
http://www.oracleinmemory.com/2016/03/acfs-automatic-cluster-filesystem-on.html
ASMCMD> ASM command line utility
http://www.oracleinmemory.com/2016/01/asm-asmcmd.html

Adding a disk to diskgroup > ASM

http://www.oracleinmemory.com/2016/09/adding-disk-to-diskgroup-asm.html

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 -- 

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) 

--Nikhil Tatineni----Dataguard--

--More links --https://jhdba.wordpress.com/2009/07/16/managing-dataguard-monitoring-scripts/

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--

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--