Monday, August 18, 2014

ORA-26947: Oracle GoldenGate replication is not enabled

When I’m trying to setup OGG On my Linux Machine after finished installing (Installation Steps) when I start my extract process it got ABENDED with the below Error message and when I see the ORA error in the error log and after researching some docs then I realized Oracle introduced a new OGG parameter in order to enable OGG replication

ERROR:

2014-08-15 18:00:38  WARNING OGG-01423  No valid default archive log destination directory found for thread 1.

2014-08-15 18:00:38  INFO    OGG-01515  Positioning to begin time Aug 15, 2014 5:44:29 PM.

----output Trimmed

2014-08-15 18:00:59  ERROR   OGG-00446  Opening ASM file +FRADG/gsource/archivelog/2014_08_15/thread_1_seq_7.302.85568
4013 in DBLOGREADER mode: (26947) ORA-26947: Oracle GoldenGate replication is not enabled.
Not able to establish initial position for begin time 2014-08-15 17:44:29.

2014-08-15 18:00:59  ERROR   OGG-01668  PROCESS ABENDING.


Solution:

For Oracle DB versions 11.2.0.4 and 12.1.0.2 there will be a new init.ora parameter called ENABLE_GOLDENGATE_REPLICATION.  In order for some of the Oracle GoldenGate functionality to work, this parameter must be set to true.

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE   SCOPE=BOTH;
System altered.

No need to Bounce the database instance

Referrence : ENABLE_GOLDENGATE_REPLICATION database parameter (Doc ID 1568417.1)


Thursday, May 8, 2014

Enable TDE in oracle Goldengate CLASSIC mode


Ref:  [Doc ID: 1451327.1]

To support TDE on Oracle 10.2.0.5 or 11.2.0.2, download and apply Oracle Patch 10395645 to the source database. Oracle 11.2.0.3 patchset includes this patch

Since my database is on 11.2.0.2  Two-Node RAC on LINUX so i'm applying below patch (see patch doc for how to apply)

$ opatch apply online -connectString<SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_Node2>:<Password_Node2>:<Node2_Name>,<SID_NodeN>:<Username_NodeN>:<Password_NodeN>:<NodeN_Name><PATCH_TOP_DIR>/10395645

From NODE 1: (everything from NODE1 unless specified)
------------------------------------------------------------------------

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DEV011

SQL> select * from gv$encryption_wallet;

INST_ID   WRL_TYPE
----------    --------------------
WRL_PARAMETER                                                        STATUS
----------------------------------------              ------------------------------------
         1   file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet       CLOSED

         2   file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet      CLOSED

 Status "CLOSED" because my database doesn't have any Wallet created yet

SQL> select * from v$encryption_wallet;

WRL_TYPE
-------------
WRL_PARAMETER                                                        STATUS
-----------------------------------                           ---------------------------
file
/opt/oracle/app/admin/DEV01/wallet            CLOSED




Put the below code in sqlnet.ora of GI and OH home location :

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/app/admin/DEV01/wallet/)))

NOTE:  Create the directories on both nodes if they doesn't exists on the server
ex: mkdir -p /opt/oracle/app/admin/DEV01/wallet

copy sqlnet.ora file to other nodes into same locations (GRID and RDBMS homes)

ex: scp sqlnet.ora oracle@oracledev012.domain.com:/opt/oracle/database/11.2.0.2/network/admin
scp sqlnet.ora oracle@oracledev012.domain.com:/opt/grid/11.2.0.2/network/admin


Now create a wallet password using below sql command from one node:

SQL> alter system set encryption key identified by "xxxxx";        
System altered.


SQL> select * from gv$encryption_wallet;

   INST_ID   WRL_TYPE
----------    --------------------
WRL_PARAMETER                                                        STATUS
-----------------------------------              ------------------------------------
         1   file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet             OPEN

         2    file
/opt/oracle/database/11.2.0.2/admin/DEV01/wallet           CLOSED



copy the wallet file from /opt/oracle/app/admin/DEV01/wallet/ from node1 to other nodes

**** Now, BOUNCE the database ****

$ srvctl stop database -d DEV01
$ srvctl start database -d DEV01

SQL> select * from gv$encryption_wallet;

INST_ID   WRL_TYPE
----------    --------------------
WRL_PARAMETER                                                        STATUS
-----------------------------------              ------------------------------------
         1     file
/opt/oracle/app/admin/DEV01/wallet/              CLOSED

         2 file
/opt/oracle/app/admin/DEV01/wallet/               CLOSED


Observe, Oracle wallet location has been changed after database bounce

SQL> alter system set encryption wallet open identified by “xxxxx";
System altered.

SQL> select * from gv$encryption_wallet;

   INST_ID   WRL_TYPE
----------    --------------------
WRL_PARAMETER                                                        STATUS
-----------------------------------              ------------------------------------
         1   file
/opt/oracle/app/admin/DEV01/wallet/               OPEN

         2 file
/opt/oracle/app/admin/DEV01/wallet/              OPEN



From GG_HOME directory, run prvtclkm.plb, and grant privilege to goldengate user
(missing this step may cause extract error:  PLS-00201: identifier 'SYS.DBMS_INTERNAL_CLKM' must be declared)


oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 18 20:59:07 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>@prvtclkm.plb

Package created.

Library created.

Package body created.


SQL>grant execute on sys.dbms_internal_clkm to gguser;
Grant succeeded.


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ cd /opt/oracle/app/admin/DEV01/wallet

From wallet location, execute below:
oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA


Now, create an entry forORACLEGG in the wallet (this will ask you to create sharedsecret password)

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line
Enter your secret/Password:enter_sharedsecret_password
Re-enter your secret/Password:enter_sharedsecret_password
Enter wallet password:enter_wallet_password

Now verify whether OGG entry has been created or not

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA


Now we’ll see the new ORACLEGG entry in the wallet (only in node1), so to create the entry in other nodes copy the wallet to other nodes

Here I’m doing SCP the wallet file from node1 to other nodes


$ scp * oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet


After copying wallet file from node1 to node2 list and see for new encryption for ORACLEGG entry from Node2 server


(from NODE2) oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA


NOTE:
- close wallet from all the instances.
- re-open the wallet from all the instances.

Ex:
alter system set encryption wallet close identified by "xxxx"; (node1)
alter system set encryption wallet open identified by "xxxx"; (node1)
alter system switch logfile;

alter system set encryption wallet close identified by "xxxx"; (node2)
alter system set encryption wallet open identified by "xxxx"; (node2)
alter system switch logfile;

Connect to ggsci and encrypt the sharedsecret password

GGSCI (oracleDEV01.domain.com) 2> ENCRYPT PASSWORD sharedsecret AES128 ENCRYPTKEY WALLETENCR
Encrypted password:  AADAAAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI
Algorithm used:  AES128

Where WALLETENCR à key mentioned in the ENCKEYS file


In the Extract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option

GGSCI (oracleDEV01.domain.com) 3> edit params EXT

DBOPTIONS DECRYPTPASSWORD AADAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI AES128 &
ENCRYPTKEY WALLETENCR


Bounce the extract process to take this effect

Thursday, May 1, 2014

Adding Disks to ASM diskgroup Manually

Oracle: $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 21 17:14:46 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER     NAME                         TOTAL_MB    FREE_MB       USABLE_FILE_MB
-----------------------         ----------------                ----------           ----------         --------------
                 1                DATA                                           221181     128900         128900
                 2                ORAARCH                                     73727      73675          73675
                 3                 REDO                                               8191       1902           1902


SQL>  select disk_number, mode_status, name, path from v$asm_disk where group_number = 3 order by disk_number, name ;

DISK_NUMBER  MODE_ST     NAME                           PATH
------------------   ------------      ---------------------       ---------------
          0              ONLINE         REDO01                         ORCL:REDO01

From above we see REDO diskgroup has only I disk(REDO01)

Now to check the available disks on server to add them to  ASM diskgroup

SQL> select path, header_status, mode_status from v$asm_disk;

PATH            HEADER_STATU     MODE_ST
--------------- ----------------      ----------
ORCL:DATA01     MEMBER       ONLINE
ORCL:DATA02     MEMBER       ONLINE
ORCL:DATA03     MEMBER       ONLINE
ORCL:ORAARCH01  MEMBER       ONLINE
ORCL:REDO01     MEMBER       ONLINE
ORCL:REDO08     PROVISIONED  ONLINE
ORCL:REDO07     PROVISIONED  ONLINE
ORCL:REDO06     PROVISIONED  ONLINE
ORCL:REDO05     PROVISIONED  ONLINE
ORCL:REDO04     PROVISIONED  ONLINE
ORCL:REDO03     PROVISIONED  ONLINE
ORCL:REDO02     PROVISIONED  ONLINE

12 rows selected.


SQL> ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02';
ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02'
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
oracle $:/opt/oracle
oracle:$  export ORACLE_SID=+ASM1
oracle :$ export ORACLE_HOME=
oracle :$ export PATH=
oracle:+ASM-> sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 21 17:41:11 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL>  ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02';
 ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group


NOTE: According to Doc ID 889810.1 logging in with SYSDBA privilege, that is not allowed for ASM operations

SQL> conn / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 21 17:42:38 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> set linesize 280
SQL> col path format a15
SQL> select disk_number, mode_status, name, path from v$asm_disk where group_number = 3 order by disk_number, name ;

DISK_NUMBER   MODE_ST    NAME                           PATH
----------------    ------------- ---------------------- ---------------
               0        ONLINE      REDO01                         ORCL:REDO01

Now add 1 disk to REDO Disk group:

SQL> ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02';
Diskgroup altered.

SQL> select disk_number, mode_status, name, path from v$asm_disk where group_number = 3 order by disk_number, name ;

DISK_NUMBER    MODE_ST       NAME                           PATH
----------------     ----------------    ------------------------ ---------------
          0       ONLINE        REDO01                         ORCL:REDO01
          1       ONLINE       REDO02                         ORCL:REDO02


SQL> select path, group_number group_#, disk_number disk_#, mount_status, header_status, state, total_mb, free_mb  from v$asm_disk order by group_number; 

PATH               GROUP_#     DISK_# MOUNT_S HEADER_STATU STATE      TOTAL_MB    FREE_MB
--------------- ---------- ---------- ------- ------------ -------- ---------- ----------
ORCL:REDO03              0          1 CLOSED  PROVISIONED  NORMAL            0          0
ORCL:REDO04              0          2 CLOSED  PROVISIONED  NORMAL            0          0
ORCL:REDO05              0          3 CLOSED  PROVISIONED  NORMAL            0          0
ORCL:REDO06              0          4 CLOSED  PROVISIONED  NORMAL            0          0
ORCL:REDO07              0          5 CLOSED  PROVISIONED  NORMAL            0          0
ORCL:REDO08              0          6 CLOSED  PROVISIONED  NORMAL            0          0
ORCL:DATA01              1          0 CACHED  MEMBER       NORMAL        73727      42968
ORCL:DATA02              1          1 CACHED  MEMBER       NORMAL        73727      42965
ORCL:DATA03              1          2 CACHED  MEMBER       NORMAL        73727      42967
ORCL:ORAARCH01           2          0 CACHED  MEMBER       NORMAL        73727      73675
ORCL:REDO01              3          0 CACHED  MEMBER       NORMAL         8191       2408
ORCL:REDO02              3          1 CACHED  MEMBER       NORMAL         8191       7683

12 rows selected.

From the above output ORCL:REDO02 now became MEMBER from PROVISIONED status

Now check the ASM rebalancing

SQL> select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;

GROUP_NUMBER    OPERA    STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE    EST_MINUTES
---------------------           ---------   ----------   ----------     ----------           ----------          ----------        -----------          ------------
                   3                REBAL             RUN           1                1             1712           3148                   1104           1

SQL> /

GROUP_NUMBER    OPERA    STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE    EST_MINUTES
------------------------        ---------      ----------    ----------     ----------        ----------      ----------         -----------          ------------
                     3              REBAL             RUN           1               1                     3142       3148                   1145           0


SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER      NAME                 TOTAL_MB    FREE_MB      USABLE_FILE_MB
----------------------        -----------------      -------------     -----------          --------------
           1           DATA                                221181     128900         128900
           2           ORAARCH                          73727      73675          73675
           3           REDO                                16382      10091          10091



After adding all the remaining disks…..


SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER          NAME            TOTAL_MB     FREE_MB          USABLE_FILE_MB
----------------------        ----------------       ---------------     ----------             --------------
           1                  DATA                               221181     128900         128900
           2                  ORAARCH                          73727      73675          73675
           3                   REDO                                 65516      59213          59213


From 11gr2 we can also add/delete/alter disks/volumes using ASMCA(ASM configuration assistant) 
see [Doc ID 885780.1]




Friday, April 25, 2014

OGG-00730 No minimum supplemental logging is enabled.

My GG version: 11.2.1.0.x
OS version:  RHEL 5.x
Oracle version: 11.2.0.2

 Extract ABENDS with below error

ERROR   OGG-00730  No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key column is not in first row piece.

This error occurs when we haven't yet enabled supplemental logging on the database level but when I check my database it has supplemental logging enabled

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
YES YES

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   6
Current log sequence           6

Solution:        This is a BUG, [1571665.1]
           Added undocumented parameter “TRANLOGOPTIONS DISABLESUPPLOGCHECK” parameter to extract and once started then removed it from paramfile and everything worked fine

Thursday, April 24, 2014

Generate GOLDENGATE Encryption Keys using Keygen


My GG version: 11.2.1.0.x
OS version:  RHEL 5.x
Oracle version: 11.2.0.2

In order to generate keys, run the keygen command from the GoldenGate software installation home

KEYGEN   key length   n

Where:
(key length) is the encryption key length, up to 128 bits.
(n) represents the number of keys to generate.


oracle@oradev01:/opt/oracle/GGATE  $ ./keygen  128 4
0x022A972B7CF6EF537DBCF35792BEH321

0x03CC8167B516426D9CA3A70B5B1FDW12

0x066E6C23EF369406BA8A5B3F2580DF54

0x0A10575F2857E61FD8710F73EFE08H11


Save the file as the name ENCKEYS in all upper case letters, without an extension, in the Oracle GoldenGate installation directory.

oracle@oradev01:/opt/oracle/GGATE $  vi ENCKEYS

oracle@oradev01:/opt/oracle/GGATE $ cat ENCKEYS
key1 0x022A972B7CF6EF537DBCF35792BEH321
key2 0x03CC8167B516426D9CA3A70B5B1FDW12
key3 0x066E6C23EF369406BA8A5B3F2580DF54
key4 0x0A10575F2857E61FD8710F73EFE08H11

Copy this ENCKEYS file to all the target servers in the GG home location. The key names and values in all of the ENCKEYS files must be identical, or else the data exchange will fail

oracle@oradev01:/opt/oracle/GGATE $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (oradev01) 1> encrypt password gguser123
Expecting either 'ENCRYPTKEY' or an encryption algorithm

we need to give encrypt key which is specified in ENCKEYS file

GGSCI (oradev01) 2> encrypt password gguser123 ENCRYPTKEY key1
Encrypted password:  AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT
Algorithm used:  AES128

Check whether you can able to connect with this encrypted password

GGSCI (oradev01) 3> dblogin USERID gguser@MYDB, PASSWORD AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT, encryptkey key1
Successfully logged into database.

GGSCI (oradev01) 3> dblogin USERID gguser@MYDB, PASSWORD gguser123
Successfully logged into database.

So you can use this encrypted password in your extract param file

GGSCI (oradev01) 4> edit params ext

GGSCI (oradev01) 6> view params ext

EXTRACT ext
USERID gguser@MYDB, PASSWORD AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT, encryptkey  key1
EXTTRAIL /opt/oracle/GGATE/dirdat/lt
………
…..
………….
SETENV NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1"
TABLE schema.table_name



NOTE:  The only way to protect this ENCKEYS file from other is through o/s privileges (Doc ID 1575452.1)


Thursday, April 3, 2014

Install Grid Agent from 11g OEM

To install Agent on the oracle linux server
Go to Deployments TAB from 11g Grid control page




Click on Install Agent, then the below screen will be appeared



Click on the links which you want to perform, here I click on Fresh install

NOTE: If we want to upgrade agent from 10g to 11gr1 then simply click on Upgrade agent, so no need to install 11g agent on the server it will automatically install 11g and upgrade from 10g to 11g.






Fill all the fields according to the environment.

Here you need to know the Management Server Registration Password  (OMS password) to continue.
Once done verify from the server : ./emctl status agent
Verify from grid control, it should show new oracle linux HOST

Run the .../agent11g/root.sh script (as root) as soon after installation is complete (or) you can check the "run root.sh" box if you have root access




Useful Standby database commands



To see if the MRP is running or not
 SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

How to Check if Active Data Guard is Already Enabled :

SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE='READ ONLY';

ADG
-----------------------                                               (if ENABLED)
Using Active Data Guard                     


no rows selected.                                                              (if NOT ENABLED)


To start Redo Apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To stop recovery of a standby database:
SQL>   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On primary:
SQL >  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;

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;

Check Archive gap on Standby:
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Check the max log sequence on Primary DB
 SELECT Max(sequence#) FROM   v$log_history;


# check the last log applied on STANDBY
SELECT thread#,   Max(sequence#) "Last Standby Seq Applied"
FROM   v$archived_log   WHERE  applied = 'YES'  GROUP  BY thread#   ORDER  BY 1;
 

SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME=’transport lag’;
SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME=’apply lag’;


On primary:  (Where dblink_stby à dblink on the primary that points to the standby database )
SQL> select scn_to_timestamp((select current_scn from v$database))-scn_to_timestamp((select current_scn from v$database@dblink_stby)) from dual;
The value returned from the query indicates the number of seconds that data on the standby lags behind the current position of the primary database.


Find Redo GAP: (on STANDBY)
SQL> select * from v$archive_gap;

thread#    low_sequence#    high_sequence#
---------------------------------------------------------------------------
2              222                   222
3              341                   342


Identify missing archive logs from above output:
SQL> select name from v$archived_log where thread# = 2 and dest_id = 2 and sequence# = 222;