goldengate 12.3 installation silent mode

1. Download file below.
123014_fbo_ggs_Linux_x64_shiphome.zip

2. After unzip, you will get the file fbo_ggs_Linux_x64_shiphome

3. create response file.
[oracle@localhost opt]$ cd /opt/fbo_ggs_Linux_x64_shiphome/Disk1/response

vi oggcore.rsp

INSTALL_OPTION=ORA12C
SOFTWARE_LOCATION=/u01/app/oracle/products
START_MANAGER=NO
DATABASE_LOCATION=/u01/app/oracle/product/12.2.0/dbhome_1
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall

4. Install in silent mode.
[oracle@localhost Disk1]$ ./runInstaller -silent -nowait -responseFile /opt/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 13755 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 1658 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-12-23_03-37-06AM. Please wait ...[oracle@localhost Disk1]$ You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2018-12-23_03-37-06AM.log
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2018-12-23_03-37-06AM.log' for more details.
Successfully Setup Software.

5. if you get any link issue, follow below.
[oracle@localhost gg123]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz12.so: cannot open shared object file: No such file or directory

[oracle@localhost lib]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/lib
[oracle@localhost lib]$ ls -lrt libnnz12*
-rw-r--r--. 1 oracle oinstall 1928046 Nov 21  2016 libnnz12.a
-rw-r--r--. 1 oracle oinstall 6568149 Nov 21  2016 libnnz12.so
[oracle@localhost lib]$ cd /u01/app/oracle/product/gg123
[oracle@localhost gg123]$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so .
---------------------------------------------------------------
we may get link issue for below libs, perform below command.
---------------------------------------------------------------
[oracle@localhost lib]$ cd /u01/app/oracle/product/gg123
ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so .
ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1 .
ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so .
ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1 .

Cloning oracle home to new home.

Cloning oracle home to new home.

In this example we have taken already installed oracle home db_1 to create another home in the same server db_2.

Source home: /u01/app/oracle/product/12.1.0/db_1
Clone home: /u01/app/oracle/product/12.1.0/db_2

Use cp command to copy source home to new clone home as below.

[oracle@oratest ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1

[oracle@oratest ~]$ cd /u01/app/oracle/product/12.1.0
[oracle@orahost 11.2.0]$ cp -rp db_1 db_2
cp: cannot open `db_1/bin/nmhs' for reading: Permission denied
cp: cannot open `db_1/bin/nmb' for reading: Permission denied
cp: cannot open `db_1/bin/nmo' for reading: Permission denied


use the runInstaller command in the new Oracle Home as below:

[oracle@oratest 12.1.0]$ cd db_2/oui/bin
[oracle@oratest bin]$ ./runInstaller -silent -clone ORACLE_BASE="/u01/app/oracle" ORACLE_HOME="/u01/app/oracle/product/12.1.0/db_2" ORACLE_HOME_NAME="OraHome2"

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 3960 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-03-12_09-46-15AM. Please wait ...[oracle@orahost bin]$ Oracle Universal Installer, Version 12.1.0.1.0 Production
Copyright (C) 1999, 2015, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2018-03-12_09-46-15AM.log
.................................................................................................... 100% Done.


Installation in progress (Tuesday, March 12, 2018 9:46:35 AM EDT)
.............................................................................                                                   77% Done.
Install successful

Linking in progress (Tuesday, March 12, 2018 9:46:43 AM EDT)
Link successful

Setup in progress (Tuesday, March 12, 2018 9:48:14 AM EDT)
Setup successful

End of install phases.(Tuesday, March 12, 2018 9:50:31 AM EDT)
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u01/app/oracle/product/12.1.0/db_2/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts
    
The cloning of OraHome2 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2018-03-12_09-46-15AM.log' for more details.


We are ready with new home db_2

Restore latest control file from primary database to standby database in oracle

You may need to restore latest controlfile from primary database for any reason  like controlfile data corruption or missing. you find the below steps to follow restore.

--In RMAN, connect to the PRIMARY database and create a standby control file backup:

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/stdbycontrolfile.ctl';

--Copy the standby control file backup to the STANDBY system(in this example /tmp/stdbycontrolfile.ctl)

--Capture datafile information in STANDBY database.

spool datafile_names.txt
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off

--From RMAN, connect to STANDBY database and restore the standby control file:

RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/stdbycontrolfile.ctl';

--Shut down the STANDBY database and startup mount:

RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP MOUNT


--Catalog datafiles and archivelog files in STANDBY if location/name of datafiles is different.

RMAN> CATALOG START WITH '<datafiles_location>';
RMAN> CATALOG START WITH '<arch files location>';


--Start mrp as below.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

oracle goldengate interview questions

1. what is suplimental logging and why its required for gg replication.
2. what is tranlog in goldengate level.
3. what is PASSTHROU parameter
4. what is ASSUMETARGETDEFS parameter
5. what is credential store
6. what is checkpoint table? which capture mode it will be used integrated/classic?
7. what is discard file. what data it will stores.
8. what si BATCHSQL mode.
9. difference between Lag at checkpoit and Time since checkpoint.
10. what is CDR.
11 difference between classic and integrated capture.
12. what all manager process can do.

password file auto resync in oracle standby in oracle 12cr2

Every time you change password for password file users like sys, system, sysdg..... in primary database, you need to copy password file to standby site every time. 

In oracle 12cr2 DBAs will have relief, any changes for the password file in database level like change password, adding new user to password file etc will automatically resync in all standby site.

This is possible as password file changes also becomes as redo.

Below the demonstration: In this demo, primary and standby are in same server with same ORACLE_HOME.

primary: orcl

standby: stdby

password files timestamp before creating new user.

[oracle@localhost dbs]$ pwd

/u01/app/oracle/product/12.2.0/dbhome_1/dbs

[oracle@localhost dbs]$ ls -lrt orapw*

-rw-r-----. 1 oracle oinstall 4.5K Aug 12 15:10 orapworcl

-rw-r-----. 1 oracle oinstall 3.5K Aug 12 15:11 orapwstdby

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 12 15:26:49 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> grant connect,sysdba to dba_user identified by dba_user;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

password files timestamp after creating new user. orcl password file showing new timestamp

[oracle@localhost dbs]$ ls -lrt orapw*

-rw-r-----. 1 oracle oinstall 3.5K Aug 12 15:11 orapwstdby

-rw-r-----. 1 oracle oinstall 5.0K Aug 12 15:27 orapworcl

switch logfile in primary:

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 12 15:27:27 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system switch logfile;

System altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

password files timestamp after switch logfile. now standby password file also showing new timestamp, so its updated.

[oracle@localhost dbs]$ ls -lrt orapw*

-rw-r-----. 1 oracle oinstall 5.0K Aug 12 15:27 orapworcl

-rw-r-----. 1 oracle oinstall 4.0K Aug 12 15:27 orapwstdby

user is updated in standby database.

[oracle@localhost dbs]$ . oraenv

ORACLE_SID = [orcl] ? stdby

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 12 15:38:17 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select username,sysdba from v$pwfile_users where username='TEST_USER';

USERNAME             SYSDB

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

TEST_USER            TRUE

EXPDP in readonly standby in oracle

Expdp is not possible while standby database is in "read only with apply"

Reason: every datapump job will create master table in database to track the job status. So the database is in readonly it cannot create master table in database and throw error.

[oracle@localhost app]$ expdp \'/as sysdba\' directory=dump dumpfile=test.dmp logfile=test.log tables=ramesh.test

Export: Release 12.2.0.1.0 - Production on Sun Aug 12 13:12:01 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

ORA-31626: job does not exist

ORA-31633: unable to create master table "SYS.SYS_EXPORT_TABLE_05"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1161

ORA-16000: database or pluggable database open for read-only access

ORA-06512: at "SYS.KUPV$FT", line 1054

ORA-06512: at "SYS.KUPV$FT", line 1042

How to trim crfclust.bdb file..

How to trim crfclust.bdb file.
------------------------------------------------------

Location:  <grid_home>/crf/db/<nodename>

$ls -lrt crfclust.bdb

As grid user:

$oclumon manage -repos resize 259200
node01 --> retention check successful
node02 --> retention check successful
New retention is 259200 and will use 4516300800 bytes of disk space

CRS-9115-Cluster Health Monitor repository size change completed on all nodes.

$oclumon manage -get repsize

CRS-9011-Error manage: Failed to initialize connection to the Cluster Logger Service

$ crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'node01'
CRS-2677: Stop of 'ora.crf' on 'node01' succeeded
$ crsctl start res ora.crf -init 

$ crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'node02'
CRS-2677: Stop of 'ora.crf' on 'node02' succeeded
$ crsctl start res ora.crf -init


Now file would be trimmed.

$ls -lrt crfclust.bdb

Exadata regular commands

To find exadata machine version.
 In database server:
$cat /opt/oracle.SupportTools/onecommand/databasemachine.xml|grep -i MACHINETYPE

Exadata storage cell server software version:

#imageinfo


cellcli> list physicaldisk;

How to find Archivelog gap in oracle standby

To find Archivelog gap in standby 


I am mentioning 2 ways to find gap:

1.
simplest way to find archivelog gap is using below single commands from primary database itself.
you can use anyone of the below queries as per your wish.

Query 1:

set lines 200
col DESTINATION for a30
col ERROR for a50
select DESTINATION,TYPE,ARCHIVED_THREAD#,APPLIED_SEQ#,ARCHIVED_SEQ#,GAP_STATUS,error from v$archive_dest_status where DEST_ID=2;

Sample output:

DESTINATION                    TYPE             ARCHIVED_THREAD# APPLIED_SEQ# ARCHIVED_SEQ# GAP_STATUS               ERROR
------------------------------ ---------------- ---------------- ------------ ------------- ------------------------ --------------------------------------------------
ORCL                           PHYSICAL                        1        29711         29712 NO GAP

Query 2:
select(select name from v$database) name,
(select max(sequence#) from v$archived_log where dest_id=1
) current_primary_seq,
(select max(sequence#)
from v$archived_log
where trunc(next_time)> sysdate-1
and dest_id=2
) max_stby,
(select nvl(
(select max(sequence#)-min(sequence#)
from v$archived_log
where trunc(next_time)> sysdate-1
and dest_id=2
and applied='NO'
),0)
from dual
) "To be Applied",
((select max(sequence#) from v$archived_log where dest_id=1)-
(select max(sequence#) from v$archived_log where dest_id=2)) "To be shipped"
from dual;

Sample output:

NAME      CURRENT_PRIMARY_SEQ   MAX_STBY To be Applied To be shipped
--------- ------------------- ---------- ------------- -------------
ORCL                    29713      29713             0             0

2:
In Primary:

To find database status and max log sequence for each thread.
select name,database_role,open_mode from v$database;
select thread#,max(sequence#) from gv$archived_log group by thread#;

To find if there is any error for the standby apply, here ERROR colomun should be null value.
set lines 200
col dest_name for a40
col destination for a30
select dest_id,dest_name,target,destination,status,error from v$archive_dest where dest_id=2;



In Syandby side:

To find database status;
select name,database_role,open_mode from v$database;

To find the received sequence and applied sequence.

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;

Remember, difference value is 0 it does not mean that standby is in sync.
Received sequence and max sequence in primary(which is taken above) should be same to confirm archives are reaching to standby from primary. if the received sequence in above query is less than max sequence in primary then there is gap in receiving archives from primary, need to find the reason according to error in alert log in primary.
If there is difference between received seq and applied seq , Then there is apply gap in standby, then we need to check standby is in recovery more or any other issue in standby based on alert log in standby.

To Check MRP is running in database level.

select process,status from v$managed_standby;

Hope it will help