GoldenGate quick reference

Quick Reference: Oracle Golden Gate Commands

The commands listed below are used in Oracle Golden Gate's Command Line Interface, GGSCI, to access the GGSCI login as the goldengate user and type ggsci at the OS command prompt.  Be sure that you are in the GGS_HOME directory in order to get valid responses to these commands.
GGSCI commands to view:
1. send status -- This will return a detailed status of the processing state, including current read and write positions.
2. stats -- This will return the transaction operations processed.
3. info all -- Gives a high level overview of all the ggs processes and their current status.
4. info detail -- Provides more detail information about a process/processes. It tells when the process started and additional information depending on the process.
5. info , showch -- Shows information about Extract/Replicat checkpoints. This includes read, write, recovery checkpoints.
GoldenGate report files:
Extract and Replicat processes generate reports file at the end of each run with the following information recorded:
> parameters in use
> table and column mapping
> database information
> runtime messages and errors
> runtime statistics for the number of operations processed

The reports can be viewed by:
ggsci>send report  -- this is to flush out the latest information to the report
ggsci> view report -- this will allow you to view the contents of the report
If a process abends without a report, run the process from the command shell of the operating system (not GGSCI) to send process information to the terminal.
Syntax:
paramfile .prm
Where:
is either Extract or Replicat.
Paramfile .prm is the fully qualified name of the parameter file.
For example:
replicat paramfile ./dirprm/E1_279QA.prm

scp and running script in background in unix

--Start the scp in nohup mode
nohup scp oracle@100.120.182.181:/dump01/rmanbkp/MIO/* /app/mjhj/hjioz_backup > scp.out 2>&1

--press control +Z key from keyboard
ctrl+z

--type bg to get the background process
bg

-To run a sql statement in the background

nohup sqlplus "/ as sysdba" @.sql &

nohup sqlplus "/ as sysdba" @.sql > file.out 2>&1 &

nohup sqlplus / @.sql &

file system trace clearance in unix

-To clear old trace files and check the large files present in the file system
find . -name *.arc.Z -type f -atime +30 -exec rm {} \;
find . -name "OCMPROD_DB*" -atime +9 -exec rm -rf {} \;
find . -name "ora_audit*" -atime +60 -exec rm -rf {} \;

du -a . | sort -n -r | head -n 50

--To kill  multiple sessions
ps -ef | grep oracle | grep gzip |awk '{print "kill -9 ",$2;}' > /tmp/killparrt

stream proces check in oracle

--Check the process status
 strmc status -d

--found the process is aborted
ORUXAX      CAP_DR_STORE_FOR_ORUXAX          ABORTED    0             Process DOWN

--Start the process by below command
 strmc start -d -n

 strmc start -d ORUXAX -n CAP_DR_STORE_FOR_ORUXAX

-Check again the stream process to make sure it is in enable state
 strmc status -d ORUXAX

-To check the que apply status
select APPLY_NAME,QUEUE_NAME,QUEUE_OWNER,STATUS from dba_apply; 

select CAPTURE_NAME,QUEUE_NAME,QUEUE_OWNER,STATUS from dba_capture; 

check the number of connection to listener file in oracle database

----Listener connection details using below UNIX command.

grep '14-OCT-2014' listener.log | grep 'establish' | awk '{print $1 " " $2 }' | awk -F: '{print $1}' | sort | uniq -c

grep '14-OCT-2014 13' listener.log | grep 'establish' | awk '{print $1 " " $2 }' | awk -F: '{print $1":"$2}' | sort | uniq -c

generate DDL of sequences from a user in oracle

-To generate the privileges for sequence
SELECT 'GRANT SELECT ON '||p.owner||'.'||p.table_name||' TO '||p.grantee||';' stmt FROM DBA_TAB_PRIVS p, DBA_SEQUENCES s WHERE p.owner = s.sequence_owner AND p.table_name = s.sequence_name AND s.sequence_owner ='';

-To get the metadata of sequence from a user

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner)
FROM   all_sequences
WHERE  sequence_owner = UPPER('&1')
AND    sequence_name  = DECODE(UPPER('&2'), 'ALL', sequence_name, UPPER('&2'));

create database link in another user account in oracle

-Grant the user required privilege to the user
GRANT create database link,create procedure TO <private_user>;

-create the procedure in the user(private_user) account
CREATE PROCEDURE private_user.cre_db_lnk AS
BEGIN
    EXECUTE IMMEDIATE 'CREATE DATABASE LINK PRIVATE_LINK '
            ||'CONNECT TO private_user IDENTIFIED BY private_user '
            ||'USING ''(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test.honeymoney.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hello.honeymoney.com)))''';
END cre_db_lnk;
/

---Execute the procedure
exec private_user.cre_db_lnk

--Revoke the privilege from the user and drop the procedure
REVOKE create database link FROM private_user;
DROP PROCEDURE private_user.cre_db_lnk;